Methods for Accessing Excel Data

[evangelmike]

Greetings! After a few hours trial and error, I failed to figure out how Section "3) VBScript via COM" of Marcus’ Macro Blog entitled "Methods for Accessing Excel Data" (July 2nd, 2007) would need to be modified if at the time one wants to use Functions GetCell and SetCell, Excel is already open and has performed some processing. I have my program set up so that it Auto_Opens with file BookDates, requests various user inputs and options, and then waits for the user to select the first Macro Scheduler macro he wishes to run. Upon selection of a macro, then I would like to use VBSCRIPT Functions GetCell and SetCell. Any help will be greatly appreciated. (I was able to get this Section 3 to work in the case where Excel is not yet open.)

Sincerely,

Michael D Fitzpatrick

[Marcus Tettmar]

Essentially you need to attach to the existing instance of Excel using GetObject instead of creating a new instance of Excel using CreateObject.

However, I've had mixed success getting GetObject to work. Since Excel is already open I would use DDE to get and set cells instead.

The following post shows a script that uses GetObject to get a reference to an open Excel app:

http://www.mjtnet.com/forum/viewtopic.php?p=19929#19929

[evangelmike]

Greetings! I have found that DDEPoke and DDERequest occasionally fail when attempting to write and read Excel spreadsheet cells, and have been using much slower Macro Scheduler subroutines to read and write in Excel. (The Macro Scheduler subroutines have never failed so far.) So I am hoping that I will somehow be able to use VBScript to read from and write to Excel Spreadsheet cells. I tried the following code which hopefully needs a few minor tweaks to work properly.

Code: Select all

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook

Sub OpenExcel
  Set xlApp = GetObject(, "Microsoft Excel non-commercial use.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
End Sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.close
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND

//Do the business
VBRun>OpenExcel
SetFocus>BookDates
VBEval>GetCell("Sheet1",5,4),theValue
MessageModal>Cell value: %thevalue%
VBEval>SetCell("Sheet1",25,2,998),nul
//VBRun>CloseExcel

But when I run this macro, I get:
"Macro Scheduler
Microsoft VBScript runtime error :429
ActiveX component can's create object: 'GetObject'
Line 7, Column 2"

So I changed line 8 to:
Set xlApp = GetObject(, "Excel.Application")
and ran the macro again.
This time, I got:
"Macro Scheduler
Microsoft Office Excel :1004
" could not be found. Check the spelling of the file name, and verify that the file location is correct.
If you are trying to open the file from your list ... (Etc.)
Line 9, Column 2"

Your help is greatly appreciated.

[Marcus Tettmar]

As I said - I have not been successful with GetObject and Excel, though others appear to have succeeded. I've only ever got GetObject to work where Macro Scheduler has already created that instance with CreateObject. There's more info on GetObject here:

http://support.microsoft.com/kb/288902

I would use DDE and I've not seen any reliability problems with it. If you are finding that is not the case you could always do a DDERequest after a DDEPoke to check the new value is there and if not repeat until it is. And with DDERequest you can check for a timeout and/or blank value and repeat until successful.

[evangelmike]

After much fiddling around, I discovered some logic involving GetObject which actually worked!!! I simplified things as much as possible by saving my workbook BookDates in my C root directory. Now I will gradually complicate things until I find something which does not work. (Programming is fun!!!)

Code: Select all

// This Logic worked!!!

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook

Sub OpenExcel
  Set xlApp = GetObject("C:\BookDates.xlsm").Application
  xlApp.visible=true
  Set xlBook = xlApp.Workbooks.open("C:\BookDates.xlsm")
End Sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.close
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND

//Do the business
VBRun>OpenExcel
SetFocus>BookDates
VBEval>GetCell("Sheet1",5,4),theValue
MessageModal>Cell value: %thevalue%
VBEval>SetCell("Sheet1",25,2,998),nul

[evangelmike]

I performed a timing study in which I ran through the "Get-Set" cycle 1000 times for DDEPoke-DDERequest and VBScript's GetCell, SetCell, in order to see which set gives the fastest speed. The results shocked me. Reading and writing to Excel 1000 times using VBScript takes 42.4 seconds. Reading and writing to Excel 1000 times using DDERequest and DDEPoke takes 18.3 seconds. If, in addition, one speeds up the DDERequest logic by using StringReplace the test time is brought down to 16.4 seconds. So, where time is an important factor, DDE is hands-down winner over VBScript, running up to a full 61% faster.

Code: Select all

// DDE vs VBScript Speed Tests

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
End sub

Sub GetExcelObject
  Set xlApp = GetObject("C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookDates.xlsm").Application
  xlApp.visible=true
  Set xlBook = xlApp.Workbooks.open("C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookDates.xlsm")
End Sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.close
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND

// Elapsed time for the following was: 42.390 Seconds
VBRun>GetExcelObject
VBEval>Timer,startSeconds
Let>TestCnt=0
Repeat>TestCnt
   VBEval>GetCell("Sheet1",5,4),theValue
   VBEval>SetCell("Sheet1",25,2,998),nul
   Let>TestCnt=TestCnt+1
Until>TestCnt=1000

VBEval>Timer-%startSeconds%,elapsedSeconds

MessageModal>GetCell-CetCell Seconds Elapsed: %elapsedSeconds%


// Elapsed Time for following was:  18.296 Seconds
VBEval>Timer,startSeconds
Let>TestCnt=0
Repeat>TestCnt
   DDEPoke>Excel,C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookDates.xlsm,R11C10,
   DDERequest>Excel,C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookDates.xlsm,R11C10,SProcFin,60
   len>SProcFin,SLen
   let>LastChar=SLen-2
   midstr>SProcFin,1,LastChar,ProcFin
   Let>TestCnt=TestCnt+1
Until>TestCnt=1000

VBEval>Timer-%startSeconds%,elapsedSeconds

MessageModal>DDEPoke-DDERequest Seconds Elapsed: %elapsedSeconds%

// Elapsed Time for following was: Seconds Elapsed: 16.453 seconds
VBEval>Timer,startSeconds
Let>TestCnt=0
Repeat>TestCnt
   DDEPoke>Excel,C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookDates.xlsm,R11C10,
   DDERequest>Excel,C:\Documents and Settings\Michael Fitzpatrick\InsiderIndustryResults\BookDates.xlsm,R11C10,SProcFin,60
   StringReplace>SProcFin,CRLF,,ProcFin
   Let>TestCnt=TestCnt+1
Until>TestCnt=1000

VBEval>Timer-%startSeconds%,elapsedSeconds

MessageModal>DDE W StringReplace Seconds Elapsed: %elapsedSeconds%

[evangelmike]

One can minimize execution time if, as much as possible, reads and writes of Excel Cells are done in VBA rather than in any of the ways of doing so in Macro Scheduler. To investigate this matter, I performed reading and writing an Excel cell 1000 times in VBA as shown below. It took less than 1.2 seconds to perform this read-write loop 1000 times. As shown previously, performing this read-write loop 1000 times using DDERequest and DDEPoke in a read-write loop in a Macro Scheduler macro took 16.4 seconds. Therefore I am currently revising all my code to have as much processing as possible done in Excel's VBA rather than in Macro Scheduler. (It should be mentioned that the code which will remain in Macro Scheduler cannot be done in Excel and probably cannot be done as efficiently by any of the other macro programs on the market.)

[code]
Option Explicit

Sub GetCellSetCellTms()
'
' GetCellSetCellTms Macro
'
Dim TestCnt As Integer
Dim theValue As Integer
Dim stopSeconds As Single
Dim startSeconds As Single
Dim elapsedSeconds As Single
' Elapsed time for the following ranged between 1.063 and 1.109 Seconds

Workbooks("BookDates.xlsm").Activate
startSeconds = Timer

For TestCnt = 1 To 1000
theValue = Cells(15, 1)
Cells(15, 2) = theValue
Next

stopSeconds = Timer

elapsedSeconds = stopSeconds - startSeconds

MsgBox "Elapsed Seconds = " & elapsedSeconds

End Sub
[/code]

[sunole]

I am using Copy for many cell range to clipboard, and pass it into variable to get faster access, it take almost zero seconds for this line with many cells

xlSheet.Range("A3:C200").Copy

also I have the app invisible and not effecting the system (Except clipboard not available).
xlApp.visible=false.