Page 1 of 1 1
Topic Options
#184817 - 2008-01-24 04:22 PM Refresh Excel ODBC link from Kix
Pieman Offline
Fresh Scripter

Registered: 2002-08-20
Posts: 29
Is it possible to have kix open a specific excel workbook/sheet and refresh the data that is currently manually refreshed via an ODBC link that has been setup within the workbook?

If the answer is yes! how is this achieved, what is the code???

Cheers
Pieman
_________________________
It takes a human hours to fcuk up what a computer can do in seconds...

Top
#184831 - 2008-01-25 12:13 AM Re: Refresh Excel ODBC link from Kix [Re: Pieman]
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Ok, wont pretend I know much about this. But how is this ODBC link accompished ? Is it implemented as some kind of formula or is it implemented as a macro (or at least, some vbscript) somehow/somewhere.

Might want to post some snippets of anything you may have.

Top
#184835 - 2008-01-25 11:09 AM Re: Refresh Excel ODBC link from Kix [Re: Shawn]
Pieman Offline
Fresh Scripter

Registered: 2002-08-20
Posts: 29
Excel uses Microsoft query to connect to the data source, in this case being an IBM AS/400 (iSeries) DB2 database, through the use of an ODBC driver from the IBM Client Access terminal emulation software, though Excel can natively talk to other DB's in this manner with builtin ODBC drivers i.e. SQL, FoxPro, Oracle, Access, etc. The MS help in Excel can give a fuller explaination.
MS Query is a menu option from "Data, Import external data" drop down menu/function.
Cannot remember whether MS Query is installed by default in a standard office install...
_________________________
It takes a human hours to fcuk up what a computer can do in seconds...

Top
#184839 - 2008-01-25 11:35 AM Re: Refresh Excel ODBC link from Kix [Re: Pieman]
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Tell you what, I'll just move this as well to the COM Scripting forum and try to answer a bit there ;\)
_________________________



Top
#184841 - 2008-01-25 11:44 AM Re: Refresh Excel ODBC link from Kix [Re: Jochen]
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Ok, now for the answer(s).

 Quote:
Is it possible to have kix open a specific excel workbook/sheet


Yes, try to steal code from one of my admin scripts that logs to an excel file

 Code:
function WriteExcel()
	dim $xl, $Name
	$header = "Server","Ip-Address","Distribution State","Installation State"
	$xl = createobject("excel.application")
	if not @error
		$ = $xl.Workbooks.Add
		$xl.Range("A1:D1").Value = $header
		$xl.Range("A1:D1").Font.Bold = 1
		$xl.Range("A1:D1").Orientation = 90
		$ = $xl.Range("A1:D1").AutoFilter
		for $i = 0 to ubound($srv)
			$xl.Range("A"+($i+2)+":D"+($i+2)+"").Value = $srv[$i]
			if $srv[$i][4] <> '-'
				$comment = $xl.Range("C"+($i+2)).AddComment($srv[$i][4])
				$comment.shape.textframe.autosize = 1
			endif
			if $srv[$i][5] <> '-'
				$comment = $xl.Range("D"+($i+2)).AddComment($srv[$i][5])
				$comment.shape.textframe.autosize = 1
			endif
		next
		$xl.Range("A1:B1").Cells.ColumnWidth = 15
		$xl.Range("C1:D1").Cells.ColumnWidth = 8
		$xl.Range("A1:D1").EntireColumn.HorizontalAlignment = 3
		$xl.Range("B2:B300").HorizontalAlignment = 1
		$xl.ActiveSheet.Name = "Deploy SP4 Status"
		$ = $xl.Range("A2").Activate
		$xl.ActiveWindow.FreezePanes = 1
		$ = $xl.ActiveWorkbook.SaveAs("$Log")
		$xl.UserControl = 1
		$bye = $xl.quit
	else
		exit(0)
	endif
	$WriteExcel = 1
endfunction


[hint: try with $ = $xl.Workbooks.Open("Path\file.xls") or something]


 Quote:

... and refresh the data that is currently manually refreshed via an ODBC link that has been setup within the workbook?


Dunno exactly, but if you find something in the Object Catalog of the Visual Basic Editor built into Excel (ALT+F11) that seem to fit, just try.
At least this is how I found most of the things I needed to automate Excel files ;\)
_________________________



Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 1539 anonymous users online.
Newest Members
Viginette, ManuvdWielNL, Sir_Barrington, batdk82, StuTheCoder
17888 Registered Users

Generated in 0.044 seconds in which 0.019 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org