Excel cannot find file error

[Joanne]

I am trying to open a workbook programmatically. I get the name of the
workbook from the listbox value generated by the optButton click event.
That is working fine. But I get an error on the line
workbooks.open fname
I suspect it has something to do with the chdir cmd and the path to the
workbooks - I don't think I am giving vba enough info to find the file,
but I don't know what to do to fix it.
Could you please review this code and point out my error?
I am doing the chdir in the optButton click event because the directory
will be different depending on which optButton is clicked.
So I am:
filling the list box
changing the directory
getting the name of the workbook
trying to open the workbook - here is where my failure is.

' Fill the list box
Private Sub obHouse_Click()
Dim SrcData As Range
Dim cCell As Range
Set SrcData = Range("rngHouse2")
With lbVendor
.Clear
For Each cCell In SrcData.Cells
.AddItem cCell.Value
Next cCell
' change to the correct sub-directory
ChDir "C:\Pricing\House"
End With
End Sub

' Open the workbook
Public Sub lbVendor_Click()
Dim fname As String
With lbVendor
fname = .List(.ListIndex) ' capture the value of the list index#
selected
Debug.Print fname
End With

Workbooks.Open fname
fname = "" 'clears fname in case they choose another opt button
Debug.Print fname
End Sub

Thank you muchly for your time and expertise
Joanne

 

[Guest]

There is no reason to Chdir. Just tell the open command everything

Workbooks.Open "C:\Pricing\House\" & fname ' & ".xls"

add the .xls if it isn't in the listbox.

 

[Joanne]

Thank you, thank you
Looks so simple once you see it written down

 

[Joanne]

Tom
That did the trick for me - works great

but now I have the problem of telling vba what sub-directory to goto to
get the correct workbook. I don't know how to pass the path to the
lbVendor.click event. It is based on which (of 5) option buttons is
clicked to fill the list box - obHouse needs to look in
C:\pricing\house, whereas obDan needs to look in C:\Pricing\Dan etc etc.

I know I can't change the sub-directory in the fill list box routine
because fname has not gotten it's value yet.

Would you be kind enough to help me along a bit more? I sure appreciate
your time and efforts. Thanks a million

Here is the code again

' Fill the list box
Private Sub obHouse_Click()
Dim SrcData As Range
Dim cCell As Range
Set SrcData = Range("rngHouse2")
With lbVendor
.Clear
For Each cCell In SrcData.Cells
.AddItem cCell.Value
Next cCell
End With
End Sub

' Open the workbook
Public Sub lbVendor_Click()
Dim fname As String
With lbVendor
fname = .List(.ListIndex) ' capture the value of the list index#
selected
Debug.Print fname
End With
Workbooks.Open "C:\Pricing\House\" & fname & ".xls"
fname = "" 'clears fname in case they choose another opt button
Debug.Print fname
End Sub

 

[Guest]

without knowing more

Public Sub lbVendor_Click()
Dim fname As String
With lbVendor
fname = .List(.ListIndex) ' capture the value of the list index#
selected
End with
Debug.Print fname
if obHouse then
spath = "C:\Pricing\House\"
elseif obDan then
spath - "C:\Pricing\Dan\"
elseif obSue then
spath = "C:\Pricing\Sue\"
elseif etc then
End if
Workbooks.Open sPath & fname & ".xls"
fname = "" 'clears fname in case they choose another opt button
Debug.Print fname
End Sub