Combine 3 workbooks into one 1

[Cpreston]

Hi

We have 3 workbooks that we want to make into one on a daily basis. (Excel 2010)

The 3 workbooks will always be in the same directory and also the same names.
The headings in all 3 workbooks are the same names
The workbooks only have one sheet and the tab is always named the same.

I would like to go into a workbook called Contacts run some code which deletes what is already in the sheet and then coipy all data that is in the 3 other workbook sheets.
So then in Contacts I have one sheet with all data from the other 3 workbooks.

Also because they all have headings I only want one set of headings.

Does anyone have any code which could do this please or any simple ideas how to perform this, thanks. (I am not up that much on VBA code foe Excel) I have googled it a lot but cant seem to find a fit to what I require.

Thanks

 

[SkipVought]

Hi,

Set up 3 separate sheets, each with a query to one of the 3 workbooks. This is a ONE TIME task. Each time you refresh the query on each of these sheets, the data from each of the workbooks will be imported to the sheet. On a 4th sheet, you will need a final query to combine the data from your 3 tables into one, using a UNION ALL query like this...

Select *
From [Sheet1$]
Union ALL
Select *
From [Sheet2$]
Union ALL
Select *
From [Sheet3$]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

I have this code which currently copies the 3 other files into the main workbook but under separate tabs. I perhaps informed incorrectly the 3 workbook sheet tabs have different names.

Sub GetSheets()
Path = "C:\Trex files\"
Filename = Dir(Path & "*.csv")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

I have tried to add your code on the Main sheet to gather all ther data but it does not work

 

[SkipVought]

First, you don't have 3 workbooks. You have 3 TEXT FILES.

If you were to use Data > Get external data > Text files... to import the data from these 3 files into 3 sheets, you would eliminate he need to delete the data prior to refreshing the queries to get new data.

Second, what are the names of the 3 sheets? Did you use Data > Get external data... to import the data from the 3 sheets into one?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

No the 3 files are downloaded from a website on a daily basis and come in as CSV files (ok we open them in excel as a tool but yes they are txt files as you pointed out)

We then need to import them into an Access database using just one main CSV file called Contacts.

This database is already set up and working, it is due to the website changes that we now get 3 separate files and not just one.
Hence the need to join them all together into the Contacts file. I want to automate this using code so I can provide s simple way for another person to create the Contacts file.

Hope that explains things better

I currently can get them all into one spreadsheet using the code above. I am now trying to get all the data from them into one sheet.

Thanks

 

[SkipVought]

Well you didn't answer my last question.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Well I did I said No, if I use Data > Get external data > Text files... then I am doing this manually

 

[SkipVought]

That was not my last question. I wa an attempt to answer your statement, "have tried to add your code on the Main sheet to gather all ther data but it does not work."

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Looks to me like a lot of extra work to go from: 3 CSV files => to Excel's 3 tabs => to Excel's one tab => to one CSV file => to Access.

I would just do that in Access - grab the 3 SCV files and dump the data into it.

Unless there is some (a lot?) manipulation of data along the way...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Hi

The issue with doing it directly into Access is that I would have to create a lot more work in Access.
Currently the contacts.csv is just a linked file and from this we do a lot of import routines.
Therefore I need to use the contacts.csv linked file

The reason I need it to have all 3 files is that another person is doing the imports so needs just one file to work with

Thanks

 

[SkipVought]

So still waiting on your method, of trying to combine the data from the 3 sheets into one, that "does not work."

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

If the three csv files always have the same names and are in the same directory, you can directly import/append them to access, import steps can be customised and written in access database (import/export specifications). This can be done once. Next create simple macro in access to execute stored specifications ([tt]ImportExportSpecification.Execute[/tt]).
Alternatively, process text files (csv) directly in vba (Input, Line Input, Write, Print statements), read lines and write to new single file. If there are headers, skip first lines in file 2 and 3.

combo

 

[Cpreston]

Yes still working at it but have gone down the SSIS route now which apart from one glitch I nearly have working.

Failing that I will have to go the Access route as sugested. But I am suprised Excel cannot do this somehow. Oh well i will get there one way or another. Many thanks for the kind replies.

 

[SkipVought]

I am surprised that Excel cannot do this somehow."

Well it CAN and HAS and WILL in the future.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}