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
|