Workbooks.Open Troubles, repost as suggested

[Guest]

I repost my question as suggested.

Bob Flanagan said:

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.

I Bob, thanks a lot for you answer.
Well, I'm not an expert VBA programmer, sorry. Yes, I'm doing all my code in userform modules, as you rightly guessed, but I've always obtained good results.
I haven't declared Public variables at all (I think...).

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

Well, here it is:

First, I load main.xls

main.xls code -- stored in ThisWorkbook
Private Sub Workbook_Activate()
mainForm.Show
End Sub

I click a CommandButton that make this sub running

main.xls code -- stored in mainForm
Private Sub pwrbdgt_Click()
Dim pathName as String 'I need to open the file power_budget.xls
pathName = ThisWorkbook.Path
Workbooks.Open (pathName & "\power_budget.xls")
End Sub

Till here, everything works fine, or so it seems

power_budget.xls code -- stored in ThisWorkbook
Private Sub Workbook_Open()
starting.Show
End Sub

The userform named starting asks the user for tha path of an existing source file to read data from
The next Sub is activated by clicking on a "starting" commandbutton (named openButton). It should read data from the source file chosen and copy them to power_budget.xls

power_budget.xls code -- stored in starting (a userform)
Private Sub openButton_Click()
Dim source as Workbook
Dim questo as Workbook
If OptionButton2 Then
Set source = Workbooks.Open(sourcePath.Value) 'THE ERROR OCCURS HERE
Set questo = ThisWorkbook
For Each sheet In source.Worksheets
sheet.Copy after:=questo.Worksheets(Worksheets.Count)
Next foglio
source.Close SaveChanges:=False
End If
starting.Hide
Principale.Show 'another userform
End Sub

Even if sourcePath.Value is right (something like datafile.xls), source assumes the wrong value, in fact it still points to power_budget.xls, instead datafile.xls

I hope my question is clearer now

Thanks a lot again!
Amarth

 

[Township of East Hanover]

Pardon my ignorance but sourcePath does not seem to be a built in function
or variable so where are you declaring the sourcePath and assigning it a
value?

Rui

I repost my question as suggested.

Bob Flanagan said:

I Bob, thanks a lot for you answer.
Well, I'm not an expert VBA programmer, sorry. Yes, I'm doing all my code

in userform modules, as you rightly guessed, but I've always obtained good
results.

I haven't declared Public variables at all (I think...).


Well, here it is:

First, I load main.xls

main.xls code -- stored in ThisWorkbook
Private Sub Workbook_Activate()
mainForm.Show
End Sub

I click a CommandButton that make this sub running

main.xls code -- stored in mainForm
Private Sub pwrbdgt_Click()
Dim pathName as String 'I need to open the file power_budget.xls
pathName = ThisWorkbook.Path
Workbooks.Open (pathName & "\power_budget.xls")
End Sub

Till here, everything works fine, or so it seems

power_budget.xls code -- stored in ThisWorkbook
Private Sub Workbook_Open()
starting.Show
End Sub

The userform named starting asks the user for tha path of an existing source file to read data from
The next Sub is activated by clicking on a "starting" commandbutton (named

openButton). It should read data from the source file chosen and copy them
to power_budget.xls

power_budget.xls code -- stored in starting (a userform)
Private Sub openButton_Click()
Dim source as Workbook
Dim questo as Workbook
If OptionButton2 Then
Set source = Workbooks.Open(sourcePath.Value) 'THE ERROR OCCURS HERE
Set questo = ThisWorkbook
For Each sheet In source.Worksheets
sheet.Copy after:=questo.Worksheets(Worksheets.Count)
Next foglio
source.Close SaveChanges:=False
End If
starting.Hide
Principale.Show 'another userform
End Sub

Even if sourcePath.Value is right (something like datafile.xls), source

assumes the wrong value, in fact it still points to power_budget.xls,
instead datafile.xls

 

[George Nicholson]

If sourcePath is a Named Range try:
Set source = Workbooks.Open(Range("sourcePath").Value)

or, probably better because it is more specific:
Set source = Workbooks.Open(ThisWorkbook.Range("sourcePath").Value)
(This assumes that sourcePath is in power_budget.xls)

--
George Nicholson

Remove 'Junk' from return address.

I repost my question as suggested.

Bob Flanagan said:

I Bob, thanks a lot for you answer.
Well, I'm not an expert VBA programmer, sorry. Yes, I'm doing all my code

in userform modules, as you rightly guessed, but I've always obtained good
results.

I haven't declared Public variables at all (I think...).


Well, here it is:

First, I load main.xls

main.xls code -- stored in ThisWorkbook
Private Sub Workbook_Activate()
mainForm.Show
End Sub

I click a CommandButton that make this sub running

main.xls code -- stored in mainForm
Private Sub pwrbdgt_Click()
Dim pathName as String 'I need to open the file power_budget.xls
pathName = ThisWorkbook.Path
Workbooks.Open (pathName & "\power_budget.xls")
End Sub

Till here, everything works fine, or so it seems

power_budget.xls code -- stored in ThisWorkbook
Private Sub Workbook_Open()
starting.Show
End Sub

The userform named starting asks the user for tha path of an existing source file to read data from
The next Sub is activated by clicking on a "starting" commandbutton (named

openButton). It should read data from the source file chosen and copy them
to power_budget.xls

power_budget.xls code -- stored in starting (a userform)
Private Sub openButton_Click()
Dim source as Workbook
Dim questo as Workbook
If OptionButton2 Then
Set source = Workbooks.Open(sourcePath.Value) 'THE ERROR OCCURS HERE
Set questo = ThisWorkbook
For Each sheet In source.Worksheets
sheet.Copy after:=questo.Worksheets(Worksheets.Count)
Next foglio
source.Close SaveChanges:=False
End If
starting.Hide
Principale.Show 'another userform
End Sub

Even if sourcePath.Value is right (something like datafile.xls), source

assumes the wrong value, in fact it still points to power_budget.xls,
instead datafile.xls