Help with Excel

[fightcancer]

I've been looking at a few threads but can't make out how to read from MS Excel without opening a new XLS every time. I need to export data from a program in CSV format once every 3-5 min, import the data into Excel so I can understand and sort it, then read that data in MS from Excel. I could keep closing and re-opening Excel but I thought it would be faster if I were able to leave the XLS open and occasionally re-import data. If that's possible, how would I modify this VB code to just pull data from the Excel file that's already open without opening another Excel app with the same file? Thanks!

Code: Select all

  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)

The data extraction requires Excel for 2 reasons. I need to make sense of random codes, and then I need to sort that data. So Separate> won't work this time.

FYI, I'm going by the example code in the following threads and the other threads listed in them.
http://www.mjtnet.com/forum/viewtopic.p ... ight=excel
http://www.mjtnet.com/forum/viewtopic.p ... ight=excel

From this thread, I'm inferring that I may want to keep closing and re-opening Excel.

If you want to do lots of GETs then why not split the function into three. One function opens Excel. The other gets the cells. The other closes Excel:

http://www.mjtnet.com/forum/viewtopic.p ... ight=excel

UPDATE: Never mind for the time being. I found this thread.
http://www.mjtnet.com/forum/viewtopic.p ... ject+excel

[fightcancer]

Moving on, I've resigned myself to closing and re-opening Excel--which is surprisingly fast. Now another obstacle: refreshing the imported data.

From Excel (2003) I would click Data | Refresh Data, but I'd like to automate it through VBScript so I can do so from MS. I recorded a macro of the Refresh Data keystrokes. It amounts to a single line.

Code: Select all

Selection.QueryTable.Refresh BackgroundQuery:=False

So I tried adding and calling this sub.

Code: Select all

Sub RefreshSheet
  Set xlSheet = xlBook.Worksheets("MyOrders").QueryTable.Refresh
End Sub

...which failed with

"Microsoft VBScript runtime error :438

Object doesn't support this property or method: 'xlBook.Worksheets(...).QueryTable'

I've also tried the lines:
Set xlSheet = xlBook.Worksheets("MyOrders").Range.QueryTable.Refresh
Set xlSheet = xlBook.Worksheets("MyOrders").Range("b2").QueryTable.Refresh
Set xlSheet = xlBook.Worksheets("MyOrders").Range(2, 2).QueryTable.Refresh
Set xlSheet = xlBook.Worksheets("MyOrders").Selection.QueryTable.Refresh
...in that same sub, but they met similar fates. As well, I tried a function (instead of a sub) with no success.

The Excel VBA help file gives an example:

Code: Select all

Worksheets(1).Range("a10").QueryTable.Refresh

...And I'm going through the tutorial on converting VBA to VBS but I don't understand most of it because I'm extremely new to VBS and somewhat new to VBA. For instance, the "Immediate" window is open, but I can't get it to show me the value of "xlSortOnValues". I gather I should be using a sub (and VBRun) instead of a function (and VBEval) to refresh. Do I need to declare "BackgroundQuery:=False" as "BackgroundQuery = 0" before refreshing?

If anyone can steer me in the right direction for refreshing data, I'd really appreciate it. Otherwise, I'll create Excel VBA macros on each PC and call the VBA macro through VBS from MS.

[gdyvig]

Hi fightcancer,

I'm not a vbscript expert either, but I recall some difficulties over when to use the set command and when to simply omit the word "set".

In my vbscript I use the set command when defining xlBook or xlSheet for the first time. It does not further restrict that book or sheet.

Set xlSheet = xlBook.Worksheets("MyOrders").QueryTable.Refresh

Instead try:

Code: Select all

Set xlSheet = xlBook.Worksheets("MyOrders") 

For the refresh statements try leaving out the word "set" in your existing statements. One of these statements might work:

Code: Select all

xlBook.Refresh
xlBook.Worksheets.Refresh
xlSheet.Refresh

Warning: I have not tested these refresj statements because I have not used Excel to import data from csv files, I usually simply read and write to xls files - just keep appending to the same xls file.

One other thing to consider. Open/update/close each time you want to update the file may be slower, but it is safer - less worry that all of your updates will be lost if the close fails. Either way you would want to do frequent saves which would slow it down anyway.


Gale

[Marcus Tettmar]

Use Set only when you create a reference to an object. No other time.

[fightcancer]

^^^ Ty M & G.

One of these statements might work:

Each one gave me runtime error 438, "Object does not support this property or method: 'xl...' ". Thank you for the ideas though. For now I'm using:

Code: Select all

VBStart

Sub XLMacro(macro)
  xlApp.Run macro
End Sub

VBEnd
VBRun>XLMacro,Refresh

"Refresh" is the name of my VBA macro in Excel.

One other thing to consider. Open/update/close each time you want to update the file may be slower, but it is safer - less worry that all of your updates will be lost if the close fails. Either way you would want to do frequent saves which would slow it down anyway.

Actually, and this is a bit weird, I'm not doing any updates or saves to Excel. I'm only using it to read. Still, I'm pleasantly surprised at how quickly MS interacts with Excel.

[fightcancer]

Why does...

VBEval>GetCell("Sheet1",%RowToCheck%,15),test
Mdl>%test%

...convert the cell contents from 2009-10-08 18:47:25.000 to 40094.7413310185? In Excel 2003 it displays as 2009-10-08 18:47:25.000 when formatted as "Text" or "General".

Never mind. It seems like Excel must be manually shut down before that cell is actually formatted as Text. The problem resolved itself over the course of an hour.