troubles with the Workbooks.Open method in Excel 97
 

Hi! I have troubles with the Workbooks.Open method.
I want to open a file (power_budget.xls) from a main workbook with a userform menu (main.xls). I used Workbook.Open("power_budget.xls") method to open it and it works well, but now the problem arises.
power_budget.xls should ask the user for the path of an excel file containing data to be imported. I stored the sub to show the form that asks for the path in ThisWorkbook with an event sub like this one:
Private Sub Workbook_Open()
userform.show
End sub
When the CommandButton I put in the userform is clicked, another event Sub should open the data file with a call like this:
Set source = Workbook.Open("datafile.xls")
then read the data and import them in power_budget.xls, but that doens't happen!
When I ran the debugger I noticed that the source doesn't contain "datafile.xls" but "power_budget.xls" again, and I can't understand why this happens and how to fix it. I know the code stored in power_budget.xls is correct, because when I open it directly (without using main.xls), everything works in the right way.
Can anyone help with this problem?
10x everybody
Amarth

troubles with the Workbooks.Open method in Excel 97
 

Amarth, without seeing more of the code, we can only make guesses. It
sounds like you are doing a lot of your coding inside userform modules and
not in regular modules. If you are also declaring public variables in the
userform modules, this typically does not work. Public variables must be
declared in regular modules.

Also, it is not clear if there is code in each of the workbooks that runs
upon opening.

I would suggest a repost of your question and pasting in some of the code
into your post.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Amarth" wrote in message
...
Hi! I have troubles with the Workbooks.Open method.
I want to open a file (power_budget.xls) from a main workbook with a
userform menu (main.xls). I used Workbook.Open("power_budget.xls") method to
open it and it works well, but now the problem arises.
power_budget.xls should ask the user for the path of an excel file
containing data to be imported. I stored the sub to show the form that asks
for the path in ThisWorkbook with an event sub like this one:
Private Sub Workbook_Open()
userform.show
End sub
When the CommandButton I put in the userform is clicked, another event Sub
should open the data file with a call like this:
Set source = Workbook.Open("datafile.xls")
then read the data and import them in power_budget.xls, but that doens't
happen!
When I ran the debugger I noticed that the source doesn't contain
"datafile.xls" but "power_budget.xls" again, and I can't understand why this
happens and how to fix it. I know the code stored in power_budget.xls is
correct, because when I open it directly (without using main.xls),
everything works in the right way.
Can anyone help with this problem?
10x everybody
Amarth