Cant find sheet
Exactly what needed.
Thanks a lot,
"K Dales" wrote:
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" wrote:
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
|