File not found ?

[jflaurin]

The code below is suppose to loop thru all the files located where the master file is and extract the value in B2 and copy it in the master file. I'm getting a file not found error at this line of code :
Set wb = Workbooks.Open(strFile, ReadOnly = True)


here's the code :
<code>

Private Sub CommandButton1_Click()
Dim strFile As String, strFolder As String, lngRow As Long
Dim ws As Worksheet, wb As Workbook


strFolder = ActiveWorkbook.Path & "\"
lngRow = 1 'Starting row number in master file
Set ws = ActiveSheet
Application.ScreenUpdating = False
strFile = Dir(strFolder & "*.xls", vbNormal)

Do While strFile <> ""
Set wb = Workbooks.Open(strFile, ReadOnly = True)
ws.Range("A" & lngRow) = wb.ActiveSheet.Range("B2").Value
lngRow = lngRow + 1
wb.Close False
Set wb = Nothing
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
</code>

 

[kjv1611]

Are any of your files Excel 2007 files? If so, then you need to also look for *.xslx, as that's the 2007 format.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Bong]

Dir is going to return a file name, no path. So if your "current directory" is not the same as the directory where the active workbook is located, the file will most likely not be found.

_________________
Bob Rashkin

 

[jflaurin]

ok the error problem is fixed, the code runs smoothly...but it doesn;t work...the value of cell B2 of each files is not copied into the master file....and I checked the cell are not empty..

hmmmm.....

 

[PHV]

Set wb = Workbooks.Open(strFile, ReadOnly[!]:[/!]= True)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886