Can you stand another question from yet another VBA newbie?
While it appears one can call a function or a subroutine from another module, I would like to know if it is possible to call another function or subroutine from another Excel spreadsheet.
I have a "Main" code "attached" to the "Main" worksheet of a workbook. This codes reads in the name of the "Target" worksheet (which the user has input into the appropriate cell in the Main worksheet). It then opens the results files from another program, one at a time, in a DO loop (the user has also input the results files' ID numbers into the appropriate cells in the Main worksheet).
' Open first results file.
Set Results = Network.ResultSet("res_" & Worksheets("Main").Range("b15"))
' Set target worksheet.
DestWorksheet = Worksheets("Main").Range("b18")
' Start inputing data at column 'C' on target worksheet.
c = 3
' Collect Component result data and place in appropriate cells.
Do While Worksheets("Main").Cells(15, c - 1) <> 0
'Call the function that collects the Flowmaster data.
'Close current results file and open the next one.
Set Results = Nothing
Set Results = Network.ResultSet("res_" & Worksheets("Main").Cells(15, c))
'Add to column counter
c = c + 1
The function is meant to collect the results and place them in the Target worksheet, in a particular order, so that I can
-get a nice little table that can be cut and pasted in to reports;
-do further calculations;
Because each Target worksheet can have a different look or format, before I thought of trying a function, I was forced to update my Main code for every Target worksheet. I was hoping that by "attaching" a function to each Target worksheet and calling it from the "Main" code this would save me some time and work ('cause I'm lazy and in a hurry). But to no avail: I keep getting "Compile error: Sub or Function not defined".
Will someone please show me the way? Or, am I expecting to much from Excel?