Importing sheet 1 of upto 30 workbooks into 1 workbook

[maudedo]

Hello

I have exported 30 records from Access into individual Excel files.

I need to create a macro that will import each sheet 1 from these 30 excel files into one Excel workbook.

Is this possible?
Also, is there anyway that we can name the Sheet tab to be the value of the excel file we are importing from?

for example

in Month.xls this will import Sheet1 from 123456.xls and change the sheet number from 1 to 123456 in month.xls then
in month xls this will import sheet1 from 122345.xls and change the sheet number from 2 to 122345 in month.xls then
etal
until all sheets have been created for all the excel files in month.xls with their representing number in the tab name.

The Unique number given to each xls file will also be the value of a2 in each document, so we can get the value from there instead.

many thanks in anticipation

 

[Zathras]

One could write a macro to do that, but for only 30 sheets, why bother. You can drag a sheet from one open workbook to another, and you can change the tab text by double-clicking and typing whatever you want. (No special characters, of course.) Thirty workbooks can be handled in less time than it would take to debug the macro.

If this is the sort of thing you will need to do more than once or twice, post back and someone can write the macro for you, if you like.

 

[maudedo]

Hi Zathras

No worries. the whole process from downloading from business objects into access, out to excel is long winded enough, which is why i wanted to keep manual interaction to minimum.

this is to be done every month, and the case numbers unique every time. therefore an automated routine is a must. i've worked out how to give the sheet name a value based on a range.value which has worked, ive copied the macro code 40 times and amended as necessary for each sheet, even deleting the sheets where data is not found in the cell range.

instead on importing the sheets, i've created paste links to each of the files that will be automatically created so this should be ok too. hopefully after test, this should just be a case of going into the template file, running the macro and paste value each of the sheets.

if i have a problem after this, no doubt i am sure i will need your help

many thanks
Donna

 

[xlbo]

Why go from BOBJ to Access to Excel ???

why not just BOBJ to excel or Access to excel - i don't see the need for the 3rd step....BOBJ also opens itself to VBA so you can actually use code to open excel and work with it as if you were coding in excel. Here is something I posted in the BOBJ forum a while back. It loops through the tabs in a report and copies each to a worksheet in a workbook:

Sub GetBOData()
Dim BoApp As busobj.Application, BODoc As busobj.Document, BORep As busobj.Report
Set BoApp = CreateObject("BusinessObjects.application")
With BoApp
    .LoginAs "username", "password"
    .Visible = True
    .Documents.Open ("Full\Path\And\File\Name.rep")
With .ActiveDocument
        .Refresh
        i = 1
        For Each rpt In .Reports
            rpt.Activate
            BoApp.CmdBars(2).Controls("&Edit").Controls(20).Execute
            Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues
            i = i + 1
        Next
    End With
End With

Set BoApp = Nothing
Set BODoc = Nothing
End Sub

Might give you something to think about...

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question