Cant find sheet

[Jim73]

Hi,

I am trying to populate one sheet with data from 120 other workbooks/sheets.
They all have the same template. When running the code below it cant find the
requested sheet. Is there a problem with my way of opening the file? I have
confirmed that the name exist and is correct.

Sub Upload()
Dim fs, f, s, fc, f1, f2

f2 = ThisWorkbook.Path

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.getfolder(f2 + "\files\")
Set fc = f.Files
For Each f1 In fc

Open f1 For Binary Access Read As #1
Sheets("Business Overview").Range("B4:c38").Copy
Windows("VB_work.xls").Activate
Sheets("temp").Range("h3").Paste
Workbooks(s).Close
Windows("VB_work.xls").Activate

Next

End Sub

/Jim

 

[K Dales]

With Scripting and binary access you are reading the file in its "raw" form:
binary digits. This eliminates the ability for Excel to interpret it as an
Excel file. What you need to do instead is to get Excel to open it; my
recommendation for that is as follows:

Dim XLApp as Excel.Application, XLBook as Excel.Workbook
Set XLApp = New Excel.Application ' Opens a new Excel session (hidden)

f2 = ThisWorkbook.Path

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.getfolder(f2 + "\files\")
Set fc = f.Files
For Each f1 In fc
Set XLBook = XLApp.Workbooks.Open(f1) ' Opens the file in the hidden
Excel session - below use XLBook to refer to this workbook:

XLBook.Sheets("Business Overview").Range("B4:c38").Copy
' Unnecessary to activate the current book since the code is running
within it
Sheets("temp").Range("h3").Paste ' - so this pastes to the current
workbook
XLBook.Close False

Next

 

[Jim73]

Exactly what needed.

Thanks a lot,

With Scripting and binary access you are reading the file in its "raw" form:
binary digits. This eliminates the ability for Excel to interpret it as an
Excel file. What you need to do instead is to get Excel to open it; my
recommendation for that is as follows:

Dim XLApp as Excel.Application, XLBook as Excel.Workbook
Set XLApp = New Excel.Application ' Opens a new Excel session (hidden)

f2 = ThisWorkbook.Path

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.getfolder(f2 + "\files\")
Set fc = f.Files
For Each f1 In fc
Set XLBook = XLApp.Workbooks.Open(f1) ' Opens the file in the hidden
Excel session - below use XLBook to refer to this workbook:

XLBook.Sheets("Business Overview").Range("B4:c38").Copy
' Unnecessary to activate the current book since the code is running
within it
Sheets("temp").Range("h3").Paste ' - so this pastes to the current
workbook
XLBook.Close False

Next