Login  | |
|
 |
Excel vbscript help - 7/20/2008 3:04:32 AM
|
|
 |
|
| |
gcdisciple
Posts: 3
Score: 0
Joined: 7/8/2008
Status: offline
|
Hi guys, I've got an issue with a vbscript program i'm writing. Its supposed to open up one file, perform a calculation on every field (Multiplying by .15 rounding up and then multiplying it by .1), then switch to another excel spreadsheet and use the information from the first to do a few more calculations before setting the result in the 2nd spreadsheets cell. Each spreadsheet has multiple tabs that I will need to switch to and then perform the calculations all over again. Right now I've gotten it to to do the first part in a single spreadsheet and checked that it works. However, trying to open up the 2nd one is causing problems. It will run and it won't throw an error or give me a sucess message. I also think that the error handling or lack therof is an issue. Each spreadsheet has text in some cells and I was unable to sucessfully implement a check to makes sure it was only numeric. As a result I think it might be screwing up some of the results. Any help is greatly appreciated I'm kind of lost at the moment..
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("C:\1.xls")
Set objWB2 = objXL.WorkBooks.Open("C:\2.xls")
'wohoo for lazy variable naming
dim value1
DIm value2
dim total
dim total2
dim startcol
dim endcol
dim startrow
dim endrow
Function Roundme(x)
If Int(x) <> x Then
Roundme = Fix(x) + Sgn(x)
else
end if
End Function
' Variables to Change per file
'Starting and Ending Columns
startcol=1
endcol=10
'1st Starting and Ending Rows
startrow=1
endrow=62
dim i
for a = startcol to endcol
For i = startrow To endrow
On Error Resume Next
if IsNumeric(objXL.cells(i,a)) and Len(Trim(objXL.cells(i,a))) > 0 Then
value1=Roundme((objXL.cells(i,a) * .15))
value2=Roundme((objXL.cells(i,a) * .1))
total = value1+value2
total2 = objXL2.cells(i,a)+75
total2 = total2 + total
objXL2.cells(i,a) = total2
Else
end if
next
next
objWB.Close
objXL.Quit
objWB2.Close
|
|
| |
|
|
|
|
|
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