Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cant find sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cant find sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can find sheet on workbook have so many sheet ? | Excel Worksheet Functions | |||
find last row in a sheet | Excel Discussion (Misc queries) | |||
Find data from one sheet in another sheet | Excel Worksheet Functions | |||
Find A Value In Sheet? | Excel Discussion (Misc queries) | |||
Find changes and add them to the new sheet | Excel Programming |