Easiest way to copy multiple worksheets to one worksheet in VBA

[08211987]

I have a VBA module in Access 2007 that does a TransferSpreadsheet ac Export running a query for a particular "division" and places the results in a worksheet. Then it does alot of polishng code to clean up the appearance and places final results in another worksheet and then saves to a directory with specific file name. These are the final 2 statements:

savefile1 = SavenameDiv & "Appliances " & textdate2 & fileext
.ActiveWorkbook.SaveCopyAs savefile1

What I want to do is then copy that ActiveWorkbook into another workbook. Then I'll go through the above cleanup for the next division and when I get to the above 2 statements for that division, I want to append that divisions data in the ActiveWorkbook to the same workbook the previous division did and I will continue that for another 7 divisions. Then once they are all done, I will save that combined workbook to a different location. I will do this twice creating 2 very large workbooks containing multiple divisions while at the same time creating multiple workbooks for the individual divisions.

I'm hoping someone could provide the code that is needed to accomplish this. I'm thinking it should be very little code. I'm just not sure what code is required to create the 2 new workbooks in a separate excel session while another one is doing the first part.

Appreciate some assistance – thanks!

 

[SkipVought]

hi,

Multiple worksheets, multiple workbooks or both?

Is this a ONE TIME exersize?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[08211987]

Sorry, I guess I wasn't clear....
There will be 14 Division Workbooks and there will be 2 Combined Workbooks that are made up of the 14 Workbooks.
Example:
Combined Workbook1 contains Div1 Workbook through Div7 Workbook
Combined Workbook2 contains Div8 Workbook through Div14 WB.

The person who built this had queries for each Div and would do the TransferSpreadsheet for each query and then do a bunch of fancy spanzy cleanup work to make the worksheet within that workbook look good and then save the file to a location. Then he built 2 large queries that contained the 7 groups of Divisions and did the TransferSpreadsheet export for both. It is either Access or Excel that times out because we are dealing with about 80 meg in both.

What I would like to do is eliminate the TransferSpreadsheet export of the last 2 large queries and instead as the individual divisions are done and doing this:

savefile1 = SavenameDiv & "Appliances " & textdate2 & fileext
.ActiveWorkbook.SaveCopyAs savefile1

I would grab the contents in ActiveWorkbook and copy the contents into another Excel Workbook and keep appending the next Divisions that belong to that "Combined Workbook" until that Division group of 7 is done. And then do the same for the other Combined Workbook.

 

[08211987]

Also, this is a nightly run. Make sense? Thanks for your patience.

 

[SkipVought]

So is the HISTORIC data significant, that is creating a new workbook each time the data for, hypothetically Division A, is created, in order to be able to search the archive of Division A workbooks to find some data?

Or is there no historic data, rather the Division A workbook always contains the latest extract?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[08211987]

Div A always contains the current data.

The users want to be able to see specific division data on a daily basis and they also want to see a combined workbook of all divisions that reside in that combined workbook.

We are not fancy, we store all data in the current day file and we keep each daily file (more for us who want to make sure the process ran correctly from the previous day due to so many crashes).

 

[SkipVought]

It would seem to me that a much more direct method might be to run a modified query that would include ALL relevant divisions, directly in Excel, via MS Query, and ignore all those workbooks and the process of opening each one etc, a rather messy and circuitous process to begin with.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[08211987]

I am not familiar with that, how might I find out how to do that? Is that something that can be scheduled? We run things in Windows Task Scheduler at specific times.

Do you or anyone else recommend a task scheduler other than what we are using (inexpensive). We run anywhere from 7 to 20 jobs depending on the day of week.

Thanks for sticking with me on this Skip.

 

[08211987]

Since I am not real clear about your suggestion about the modified query, I've been reading that the TransferSpreadsheet cannot handle more than 65,000 records which this query and many do and I think that might be my problem. Below is the statement that I used to have that I have commented out:

'DoCmd.TransferSpreadsheet acExport, 10, "qry_Building_Outdoor_Products", "\\msfs05.xxxx.com\DATA1\SHARE\merchnet\PCM Reporting\PCM PSSA Reporting\Daily Status Reports\TemPlates\BigTemplateGMM.xlsx", False, "Data2"

I read to try the OutputTo statement and I tried this but it does not like it's syntax, would you know why?:

DoCmd.OutputTo acOutputQuery "qry_Building_Outdoor_Products", acFormatXLS, "\\msfs05.xxxx.com\DATA1\SHARE\merchnet\PCM Reporting\PCM PSSA Reporting\Daily Status Reports\TemPlates\BigTemplateGMM.xlsx", False, "Data2"

I found the above information from the attached url:

http://windowssecrets.com/forums/sh...-to-export-more-than-65-000-records-from-2007

 

[08211987]

I think I am missing a comma after acOutputQuery. I have a different db running now and will try it later.

 

[08211987]

The above statement did nothing for me, so now I am trying a different approach. I found this Thread in the below url:

http://www.ozgrid.com/forum/showthread.php?t=16343

I have the following questions about that thread:

And I am curious what the "FilePth" represents in the Fname = statement.
Where must the Master.xlsx reside (I would use XLSX)? Do I need to fully qualify the path of where Master.xlsx resides?
Will I need to delete the contents of the Master.XLSX file before I start this process the next night?

Everything below is from the above URL:
Thanks, C

This code assumes you have a file named "Master.xls". It will open all Excel files in a given directory (C:Temp) and copy the first sheet of each file to the end of "Master". This can be used to compile the sheets from each workbook into a single Master workbook.After which you can copy and paste (use code if necessary) to one sheet

VBA:
Sub Combine()

Fpath = "C:Temp" ' change to suit your directory
Fname = Dir(FilePth & "*.xls")

Do While Fname <> ""
Workbooks.Open Fpath & Fname
Sheets(1).Copy After:=Workbooks("Master.xls").Sheets(Workbooks("Master.xls").Sheets.Count)
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop

End Sub

 

[PHV]

Fpath = "C:\Temp\" ' change to suit your directory
Fname = Dir(Fpath & "*.xls")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[08211987]

Hey PHV, haven't talked to you in a while, I used to be surfside1 and bugged you many times.

The statement I am stumped on now is below, I'm getting an error "Run-time error '9': Subscript out of range"
Sheets(1).Copy After:=Workbooks(Master1).Sheets(Workbooks(Master1).Sheets.Count)

To be honest I am not sure what that statement is trying to do. I have Master1 set to a specific xlsx file in a directory, such as Master1 = c:\dir1\Master1.xlsx. That is the file that I want to copy all the separate xlsx files residing in Fpath. When I hover over Master1 in both places in that statement, it displays the correct path, when I hover over the Sheets.Count I get the error.

Thanks,
C

 

[08211987]

I'm not sure what "Sheets" is referring to.
Thanks,
C

 

[SkipVought]

Is Master1 the NAME of the workbook, in which case it ought to be something like

Sheets(1).Copy After:=Workbooks("Master1.xlsx").Sheets(Workbooks("Master1.xlsx").Sheets.Count)

or is it a VARIABLE, in which case, what is the VALUE of Master1 at the debug?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[08211987]

Master1 is a variable:
Master1 = "C:\Documents\GMM_Masters\GMM_BuildingOutdoor.xlsx"
And when debugging when I hover over the variable it is set correctly but when I hover over Master1 in both places of that statement it shows the directory and file name, but when I hover over Sheets.Count it shows Sheets.Count = <Subscript out of range>

Not the way to use Master1?

 

[SkipVought]

In your IMMEDIATE Window enter this...

?thisworkbook.Name

and observe the NAME returned.

This is the NAME or Name Structure, that you want to have in your variable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[08211987]

A coworker ended up creating an Excel macro that runs like a charm. But thanks for your help.

I do have another thread about the TransferSpreadsheet thread707-1687514, wondering if that can be used for to export a table like it does for a query.

Thanks,
C