PDA

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.