View Full Version : Solved: Can I 'pull in' a worksheet from one excel file into another one?
Lester
10-31-2007, 06:59 AM
Again, excuse my ignorance on the subject (...hence the question).
I have created an excel file which has 1 worksheet in it.
I have another excel file into which I want to 'pull in' (import?) this excel file. Is this possible? if so, how?
Moreover, I would like to automate this' import' process in a macro. Can this be done?
Many thanks
Cheers.
Bob Phillips
10-31-2007, 07:29 AM
Dim oWB As Workbook
Dim oThis As Workbook
Set oThis = ActiveWorkbook
Set oWB = Workbooks.Open("C:\test\Read only.xls")
oWB.Sheets("Sheet1").Move after:=oThis.Worksheets(oThis.Worksheets.Count)
Lester
10-31-2007, 10:03 AM
Thanks, xld. I replaced 'Read only.xls' with my file name.
Do I need to do any other replacements, because I get a compile error on the last line. i.e. the line
oWB.Sheets("Sheet1").Move after:=oThis.Worksheets(oThis.Worksheets.Count)
is highlighted in yellow by the debugger.
Note: There is no other "Sheet 1", so there's no conflict in that respect.
Cheers
lucas
10-31-2007, 10:36 AM
sheet1 is refering to a sheet to copy from the Read only.xls
You will have to change that also if the sheet you wish to copy is not sheet1
Lester
10-31-2007, 02:10 PM
sheet1 is refering to a sheet to copy from the Read only.xls
You will have to change that also if the sheet you wish to copy is not sheet1
Many thanks, Lucas. I'll give it a try.
Lester
10-31-2007, 03:45 PM
@lucas...I tried it and it works fine.
Cheers.
and Cheers to xld, also.
Both very helpful.
Lester
11-01-2007, 02:38 AM
Strange...the above code (plus correction) worked fine for my example at home...now when I try it at on an equivalent example at work I get RUN TIME ERROR '1004' message box:
A workbook must contain at least one visible worksheet.
To hide, delete, or move the selected sheet(s), you must first insert a new sheet or unhide a sheet that is already hidden.
Options are 'End' and 'Debug'...the latter highlights the last line, as shown below.
Sub ImportLookUp()
Dim oWB As Workbook
Dim oThis As Workbook
Set oThis = ActiveWorkbook
Set oWB = Workbooks.Open("G:\temp\ChargeRatesLookUp.xls")
oWB.Sheets("Charge Rates Look-up").Move after:=oThis.Worksheets(oThis.Worksheets.Count)
End Sub
The worksheet name 'Charge Rates Look-up' does exist (as does the file/location), so that can't be the problem. Does anyone know what I've done wrong? :(
Please help.
Thanks
Lester
Bob Phillips
11-01-2007, 03:59 AM
Sounds like oWB only has one visible worksheet, so you can't move it as it leaves the wb with none.
Try changing Move to Copy.
Lester
11-01-2007, 04:07 AM
Sounds like oWB only has one visible worksheet, so you can't move it as it leaves the wb with none.
Try changing Move to Copy.
Excellent...and I was wondering why the file I pulled across suddenly went blank! Cheers, xld.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.