Logical Error in Excel Automation

Hi, I have the following code in my ASP .net Application:

For i = 1 To Session("length")
oSheet = oBook.Worksheets(i)
temp = 35
If Session("equipment1" & cstr(i)) = "" Then
oSheet.Range("C34") = ""
Else
oSheet.Range("C34") = Session("equipment1" & cstr(i))
For j = 2 To 3
If Session("equipment" & cstr(j) & cstr(i)) <> "" Then
oExcel.Run("AdEquipment")
oSheet.Range("C" & cstr(temp)) = Session("equipment" & cstr(j) & cstr(i))
temp = temp + 1
End If
Next j
End If
Next i


For a test case, I have:
Session("equipment12") = 1
Session("equipment22") = 2
Session("equipment23") = 3
the rest are all empty

Session("length") = 3

In the excel documet on the first sheet everything is fine, on the second I get all three session variables displayed in the correct places, but the AdEquipment was never hit twice like it is supposed to. Here is the quirky part: in the third sheet it presses the AdEquipment twice like it was supposed to on the second sheet. It blanks out the first cell ("C34") and the other two are left with the default text.

If you want to look at the excel document get it from here:
http://www.finite-state.com/test/1.xls

Please can anyone help with this logical error?

Thanks.

I think I may know what the problem is, but do not know how to correct it. I think it has to do with the oExcel object. I think I need to somehow specify which workbook I want this marco to execute on. Any ideas?

oBook looks like it's supposed to refer to a workbook. What's the value of oBook?

In general, you'd want to call: oExcel.Run("'" & <workbookname> & "'!" & <macroname>) So in your case, it might look like oExcel.Run("'Book1.xls'!AdEquipment") Hope it works for you!

-- Mike

[Edit: Based on Herilane's observation, you might need: oExcel.Run("'" & oBook.Name & "'!" & "AdEquipment") -- Mike]

Hi, doing what Mike suggested, it still works the same. It's working on the right workbook but not the right worksheet. I meant to say worksheet instead of workbook, in my post.
My oBook looks liek this:

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add(oTemplate)

Better to have your code placed within a standard Code Module and not within a Worksheet in this case... But the syntax in this case would be: "'Book1.xls'!Sheet1.MyMacro" (you'll have ta translate the WorkbookName, WorksheetName and MacroName for your purposes here, but be sure to use the surrounding quote (') charatcters and the exclamation point (!)...

Hey, I put use the following code:
oExcel.Run("'" & oBook.Name & "'!" & "Year" & cstr(i) & _
"." & "AdEquipment")

And then get the following error:
The macro ''FastLane1'!Year2.AdEquipment' cannot be found.

But, that is the correct name of the worksheet, and the oBook.Name worked before without the Year2 after it, so I don't think the oBook.Name is wrong.

I think we are barking up the wrong tree here:

(1) You did not get a "Macro could not be found" error before, so I'm not sure that specifying it like this is correct.

(2) Do you really have a separate Sub AdEquipment() routine within the Class Modules of several different Worksheets?

Assuming that #2 is yes, you might consider making ONE routine that can be called with a WorkbookName paramater, thus removing the redundancy and the confusion as to which one you are calling.

Lastly, I'm curious why your oBook.Name is not comming out with the ".xls" extention at the end. Is the Workbook not Saved yet? Or is your Folder Options set to "Hide known file extentions"?

(Sorry I don't mean to be confusing, but something feels like we are talking "Apples vs. Oranges" here a bit... So let's get it straightened out!)

-- Mike

What I have is a template, which in it, I have to press another macro, which adds more worksheets. I'm not so sure how to answer you other questions, but could you download the sample I have the URL for in a previous post. No the doument has not been saved yet. I have to go, I'll be back later tonight to check the posts. Thanks again for all of your help!

Ok, I took a look. Your Sub AdEquipment() is within the Module1 code module. To call it, using oExcel.Run("AdEquipment") should work fine. If not, then oExcel.Run("'" & oBook.Name & "'!" & "AdEquipment") should do the trick.

If you are not sure if it is being called or not, add a MsgBox to the Sub so that you can be certain.

My guess is that this is not working the way you wish/expect due to the If..Then conditions set in your 1st Post, but it's hard for me to analyze, it's a pretty big workbook with a lot of code...

Hi, I'm back from being sick. Now I'm back to this problem again. It is still giving me that error: The macro ''FastLane1'!Year2.AdEquipment' cannot be found. Anymore ideas?

Sorry you were sick for so long :( Hope all is ok now...?

Your Macro is in Module1, not in a Worksheet, so you should try calling this oExcel.Run("'" & oBook.Name & "'!" & "AdEquipment") Let us know...

-- Mike

Hey thanks, yeah I was sick with a virus for almost 4 wks.! Ahhhhh. :( Anyways, yeah I tried what you suggested, a while ago, and I get I get a this: http://www.finite-state.com/test/FastLanePetersonErich.xls . It adds the two additional equipment fields to the third year, which is not the year it should be adding it to. It should be adding them to year two, but it does add the correct numbers in year two, it just doesn't seem to add the fields to the correct year before it adds the numbers to the sheet.

Thanks

Quick correction:
In my original post it is supposed to read: Session("equipment32") = 3, the first number represents which equipment number it is, and the second represents the year.

Ugh, sorry to hear that, but glad you are better!

There's a lot going on here, you have a large workbook, and I think you will have to trace this out yourself, unfortunately. No one can know your code better than you, so you're gunna have to roll up your sleeves here... If you get stuck at a particular point, then you can come back with questions, but be sure to show the code that is not working right.

Yeah, unfortunately the excel is not my code, it's the National Science Foundation's! The ASP is of course mine, anyways thanks for all your help.