Get data from Excel

[Paulo_05]

Hi all,


Now my problem is: I have an Excel spreadsheet that will perform lots of calculations based on an external database.


But sometimes there is some important information that I need MacroScheduler to retrieve data.


What do you think about (and how to implement) ?

Is it possible to have Macroscheduler to check continuosly for an especific cell position in Excel? If it changes, Macroscheduler can copy this information. And then I can retrieve it, copy to another spreadsheet, etc...

Or I can also write data into a file and then Macroscheduler can retrieve data from there....

which solution is the fastest one ?

[ainterne]

I hope this helps, not sure if its exactly what you want,

Code: Select all

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = false
  Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
   xlApp.quit
   Set xlApp = Nothing
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

//Set a loop to keep running the check.
Let>k=1
Repeat>k

      //So here you open the file you want to extract data from
      //and this uses the function above called "OpenExcelFile"

VBRun>OpenExcelFile,C:\tt\MattsProj\CC1.xls

      //The you want to get a value from a Cell in the spreadsheet.
      //So you use the function above called "GetCell"
      //It has a format of GetCell(Sheet,Row,Column). The sheet name refers to the sheet in the spreadsheet,
      //Mine happens to be called CC1.
      //The k and z value to refer to row and Column. So if you wanted to get Row1 Col3 you could have.

 Let>k=1
 Let>z=3
    VBEval>GetCell("CC1",%k%,%z%),theValue1
    ReadFile>theValue1,content
    MessageModal>content

     //So now we can compare your latest data you have pulled from the speadsheet against any earlier value you read from the file.

   ReadFile>%temp_dir%\mycellextract.txt,oldData

      If>content=oldData
      Goto>Jump
      Endif

    //So we know that the data is newer or different than the oldData and or there is no oldData.
WriteLn>%temp_dir%\mycellextract.txt,get1,%content%

//Close the spreadsheet.
VBEval>CloseExcel

      //So now we can put the data in the new spreadsheet using. SetCell(Sheet,Row,Column,NewValue).
      //However you have to open or define that spreadsheet using your OpenExcelfile function and know the sheet name you are going to populate.
      //You can run the spreadsheets in hidden mode by changing the xlApp.visible = false and non hidden using true in the function above.

VBRun>OpenExcelFile,C:\tt\MattsProj\someother.xls
VBRun>SetCell(sheetName,Row,Column,%content%)
VBEval>CloseExcel

Label>Jump

     //So now we could just to a wait> here for any time span you would like in seconds.
     //You could just run this Macro on a timer....there are a few ways to do it.

Wait>60

Until>k<1

[kpassaur]

First, thanks for the script above - execellent notes.

Okay, what I am confronted with is reading an Excel file and processing data in it. I really just need the starting row to read. The rest I can easily do in MS. The difficulty is that it is a rather large spreadsheet, 35 thousand rows and growing.

I need to find the starting row based upon the contents of a cell that contains a date.

I can read each row and column but it takes to long. What I am using is here:

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = false
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
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

VBEND


//Input>slogname,Browse to the Scan Log File Folder

Let>slogname=C:\Documents and Settings\Keith Passaur\Desktop\Processing_Log_30_07_2009.txt
Separate>slogname,_,datearray

Let>sday=datearray_3
Position>0,%sday%,1,t
If>%t%=1
MidStr>%sday%,1,1,sday
Endif

Let>smonth=datearray_4
Position>0,%smonth%,1,t
If>%t%=1
MidStr>%smonth%,2,1,smonth
Endif

Let>syear=datearray_5
StringReplace>syear,.txt,,syear
Let>scandate=%smonth%/%sday%/%syear%

Let>Excelfilename=C:\Documents and Settings\Keith Passaur\Desktop\test.xls


//So here you open the file you want to extract data from
//and this uses the function above called "OpenExcelFile"

VBRun>OpenExcelFile,%Excelfilename%

//The you want to get a value from a Cell in the spreadsheet.
//So you use the function above called "GetCell"
//It has a format of GetCell(Sheet,Row,Column). The sheet name refers to the sheet in the spreadsheet,
//Mine happens to be called CC1.
//The k and z value to refer to row and Column. So if you wanted to get Row1 Col3 you could have.


let>xlRow=12

let>xlSheet=1
let>xlRow=12
let>xlColumn=5
Label>Startread
VBEval>GetCell(%xlSheet%,%xlRow%,%xlColumn%),TestVal


If>%TestVal%=%scandate%
MDL>Starting Row Number is %xlRow%
Goto>EOF
Endif
Message>%TestVal%

Let>xlRow=%xlRow%+1
Goto>Startread

VBEval>CloseExcel

//So now we can put the data in the new spreadsheet using. SetCell(Sheet,Row,Column,NewValue).
//However you have to open or define that spreadsheet using your OpenExcelfile function and know the sheet name you are going to populate.
//You can run the spreadsheets in hidden mode by changing the xlApp.visible = false and non hidden using true in the function above.


The difficulty is that it takes minutes to find the row. Is there a faster way to do this?

[Marcus Tettmar]

Use Excel's Find method. Here's a function you can add to your VBScript block:

Code: Select all

Function FindCell(Sheet,Data)
  Dim theCell
  Dim xlValues
  xlValues = -4163

  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Range("A1").Select
  Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)

  FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
End Function

This returns ROW:COLUMN. So you could use it thus:

VBEval>FindCell("Sheet1","20090730"),cell
Separate>cell,:,cell_coords

Now you have row in cell_coords_1 and column in cell_coords_2 and can start your processing from there.

HTH

[kpassaur]

Marcus,

I added the block and I am getting a VBScript runtime error

Object required 'theCell'

Am I missing something?

Also, can I set the row to search for or do I need to do the entire sheet?

Thanks

kp

[Marcus Tettmar]

Where have you added the code. It *MUST* be added inside your EXISTING VBscript block because it refers to objects you have set up in there (xlApp, xlBook etc). If on it's own you will get errors.

Yes, you can set the start position. At present it starts after the active cell. Please look at Microsoft's documentation:

http://msdn.microsoft.com/en-us/library ... e.11).aspx

Hint: record a find operation in Excel then look at the VBA. This shows you all the options in the Find method. Convert it to VBScript to make it run from Macro Scheduler. See: http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/

That's what I did to answer your question (I'm not Microsoft).

[kpassaur]

Marcus,

I did add it to my block

VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = false
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
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

Function FindCell(Sheet,Data)
Dim theCell
Dim xlValues
xlValues = -4163

Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Range("A1").Select
Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)

FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
End Function
VBEND

Any ideas?

[Marcus Tettmar]

I don't get any errors at all with your code. I'm using:

Code: Select all

VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = false
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
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

Function FindCell(Sheet,Data)
Dim theCell
Dim xlValues
xlValues = -4163

Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Range("A1").Select
Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)

FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
End Function
VBEND

VBRun>OpenExcelFile,%SCRIPT_DIR%\example.xls
VBEval>FindCell("Sheet1","QTY"),cell
MessageModal>cell

Works nicely.

What line is the error reported on?

[kpassaur]

I am getting VBScript runtime error :424
Object required: 'theCell'
Line 35, Column 2


This is the complete script

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = false
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
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

Function FindCell(Sheet,Data)
Dim theCell
Dim xlValues
xlValues = -4163

Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Range("A1").Select
Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)

FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
End Function
VBEND


//Input>slogname,Browse to the Scan Log File Folder

Let>slogname=C:\Documents and Settings\Keith Passaur\Desktop\Processing_Log_30_07_2009.txt
Separate>slogname,_,datearray

Let>sday=datearray_3
Position>0,%sday%,1,t
If>%t%=1
MidStr>%sday%,1,1,sday
Endif

Let>smonth=datearray_4
Position>0,%smonth%,1,t
If>%t%=1
MidStr>%smonth%,2,1,smonth
Endif

Let>syear=datearray_5
StringReplace>syear,.txt,,syear
Let>scandate=%smonth%/%sday%/%syear%

Let>Excelfilename=C:\Documents and Settings\Keith Passaur\Desktop\test.xls


//So here you open the file you want to extract data from
//and this uses the function above called "OpenExcelFile"

VBRun>OpenExcelFile,%Excelfilename%

//The you want to get a value from a Cell in the spreadsheet.
//So you use the function above called "GetCell"
//It has a format of GetCell(Sheet,Row,Column). The sheet name refers to the sheet in the spreadsheet,
//Mine happens to be called CC1.
//The k and z value to refer to row and Column. So if you wanted to get Row1 Col3 you could have.
let>xlSheet=1

VBEval>FindCell(%xlSheet%,"%scandate%"),cell
Separate>cell,:,cell_coords
MDL>cell

[kpassaur]

When I run this - your sample, only thing changed is the path to the spreadsheet it fails with the same error. I double checked I have a "Sheet1"


VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = false
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
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

Function FindCell(Sheet,Data)
Dim theCell
Dim xlValues
xlValues = -4163

Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Range("A1").Select
Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)

FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
End Function
VBEND

VBRun>OpenExcelFile,C:\Documents and Settings\Keith Passaur\Desktop\test.xls
VBEval>FindCell("Sheet1","QTY"),cell
MessageModal>cell

[Marcus Tettmar]

What version of Excel are you running?

[kpassaur]

2007

[Marcus Tettmar]

I'm running 2007 also.

Care to send me your XLS file?

[kpassaur]

Marcus,

Just sent it.

Thanks for looking at it. I thought maybe it was corrupt so I opened it, saved it with a different name etc with the same results.

[gdyvig]

Hi kpassaur,

Does the Excel Find command work when you are editting Excel manually for that cell?

A google search for "Excel runtime error 424" turns up what may be some clues. Some people were having problems with formulas, broken references, protected cells, cell width not wide enough - anything that may prevent the cell from displaying the data.

Does your script work when you try to search for other data on that spreadsheet?

Does your script work if you reduce the number of rows or columns in the spreadsheet?


By the way, google search is how I found Macro Scheduler when looking for a solution to another problem.


Gale