Login  | |
|
 |
RE: VBScript & Excel - 3/23/2006 8:03:49 AM
|
|
 |
|
| |
ebgreen
Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline
|
VBScript can work with multiple files. You have a couple of options on how you want to do this. The first method is if you know how many files you will need to work with, just Dim and instantiate as many Excel objects as you need. If you don't know (for instance you need to do something to every excel file in a specific folder) then you can use a for next loop and just set one excel object to each file in turn. If you have some need to have an excel object for every excel file in a folder, then do the same thing, but add each file to a dictionary or array as you loop through them.
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: VBScript & Excel - 3/24/2006 3:28:43 AM
|
|
|
|
| |
ebgreen
Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline
|
Lets walk through your code together. I won't use code tags so that I can make comments using colored text:
'This script collects data from Excel1(employee names and work hrs) -> WS1,WS2, sums the data
'and outputs the result to Excel2 within appropriate worksheets (each employee has a worksheet)
'The two excel files for data collection
strPathExcel1 = "C:\Work\script\excel1.xls"
strPathExcel2 = "C:\Work\script\collection\excel2.xls"
'Create excel instance, run in background
Set oXL = CreateObject("Excel.Application")
oXL.Visible = False
oXL.DisplayAlerts = 0
'Open WB1->worksheet 1 as read only
Set objWB1 = oXL.Workbooks.Open(strPathExcel1,False,True)
Set Worksheet = oXL.Sheets(1)
'WB1, WS1 currently activated
'collect all 19 employee names and monthly work hrs
Dim empArray() Here you create an array with no elements
'employee names listed in column B from row 363 to 381
'|Name-|---------Monthly work hrs --------|
'Name1 Jan Feb Mar Apr May Jun Jul Aug Sep
'XXX X X X X X X X X X <--row 363
'...
'XXX X X X X X X X X X <--row 381
For i=363 To 381
'store employee name in 0 element
empArray(0) = oXL.Cells(i,2).Value Here you try to set the first element of the array to some value (remember that you explicitly told the interpreter that the array had no elements at all)
WScript.Echo empArray(0) Here you try to retrieve the value of the first element in an array that has no elements
'collect work hrs for employee i
Acount = 1
For j = 17 To 25 'Jan - Sep
empArray(Acount) = oXL.Cells(i,j).value Here you try to set the value of the second element in the array. The array still has no elements at all
ReDim Preserve empArray(Acount) Here you finally try to tell the interpreter that the array should have 2 elements
WScript.Echo empArray(Acount) Here you try to retrieve the value of the second element in the array
Acount = Acount +1
Next
Next
Note that these comments are only applicable for the first pass through the loop but that is really a moot point since code execution will never get to the loop in the first place because you try toi assign a value to the first element in an array that you explicitly declared as having no elements. You need to rethink the logic in the way you handle the array.
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: VBScript & Excel - 4/7/2006 12:50:13 AM
|
|
|
|
| |
ebgreen
Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline
|
Ok, on your search question, what is the verbatim text of the error?
For the shading question, you will need to look at each cell's .ColorIndex property. You may also need to look at its .Pattern and/or .PatternColorIndex property(ies). Note that I had no idea how to do this before you asked the question. Here is what I did to find out:
1) Open a new Excel spreadsheet.
2) Click Tools->Macros->record (pick any name for the macro)
3) Click on any cell
4) Click Format->Cells->Patterns and select the cell shading that you want to look for.
5) Stop the macro recording
6)Click Tools->Macro->Macros and select the macro that you just recorded.
7) Click the Edit button
8) Voila...there is sample code on what you are interested in.
The macro recorder should always be one of your first three stops when you have office automation questions (the other two are the docs and google).
_____________________________
"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
|
Printable Version
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread