Excel cannot find file error
 

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

Excel cannot find file error
 

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.
--
Regards,
Tom Ogilvy


"Joanne" wrote:

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

Excel cannot find file error
 

Thank you, thank you
Looks so simple once you see it written down
Tom Ogilvy wrote:

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.

Excel cannot find file error
 

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

Excel cannot find file error
 

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

--
Regards,
Tom Ogilvy

"Joanne" wrote:

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