combine 2 workbooks

Dell / GX520
March 16, 2009 at 14:49:23
Specs: Windows XP, Pentium 4 3.00GHz
I am still very new to coding and have run into a snag. I have a report that i export to excel every day. At the end of the week I have to copy each worksheet into one workbook, each in their own tab. I have looked all over the place to find a script to do this, and the closest i have come is a third party app which still has to be performed manually and a script that will grab all the excel worksheets in one folder and merge them into one worksheet. I am really trying to steer away from third party apps for this task. If anyone has any advise i would love to hear it.

See More: combine 2 workbooks

Report •

March 16, 2009 at 15:09:33
Another thing that might work for me is instead of combining multiple workbooks into one worksheet, If anyone knows of a way to script a way of changing the worksheet name to the current date that would be perfect as well then i could direct my current script to create that tab with the generic name. In other words say the constant worksheet name is alert if i import it again with another worksheet with the name alert it will either append or overwrite based off of my commands but if i change the worksheet name to the days date the next days import will be the constant name again. then i can change that name to the days date and so on. Any ideas or takers for a challenge?

Report •

March 16, 2009 at 22:28:34
that can be done in vbs. or can also with vba inside the workbook, or other languages. but if you dont have the programming knowledge, it's useless to try.

as start, why not you post what code have you done so far here.

Report •

March 18, 2009 at 21:39:57
You want to what, just make one workbook that has all of the worksheets?

Report •

Related Solutions

March 19, 2009 at 07:33:36
Actually, i got a VBscript working that will set the worksheet name to the date or whatever else name you want it changed to. I'll post the code at the bottom. Its wierd though the application i'm working with to extract information from reports into excel still only recognizes what the tab was originally named, not the date so it appends it to the existing report. Maybe its something on my script thats only renaming the text and not the actual name of the tab. I'm clueless at this point, if anyone has any advise i'd be happy to hear it. here is the current code

Dim xlApp, xlWS

Set xlApp = CreateObject("excel.application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("sheet1")
Function Format(vExpression, sFormat)

set fmt = CreateObject("MSSTDFMT.StdDataFormat")
fmt.Format = sFormat

set rs = CreateObject("ADODB.Recordset")
rs.Fields.Append "fldExpression", 12 ' adVariant


set rs("fldExpression").DataFormat = fmt
rs("fldExpression").Value = vExpression

Format = rs("fldExpression").Value

rs.close: Set rs = Nothing: Set fmt = Nothing

End Function

With xlApp.Workbooks.Open("c:\test\Test.xls")
For Each xlWS in .Worksheets
if = "test" then = format(now(), "mmddyyyy")
End IF
.Close True
End With true


Set xlApp = Nothing

Report •

March 19, 2009 at 10:57:07
Ok i got it figured out finally . i'll post it in here for others that might need it in the future. I couldn't get it to work with straight VBS code so i had to mix vba and vbs. If anyone has an idea on how to get it working straight vbs that would be helpful but this will work for now.

Here is the vbscript portion:

Dim DocToPrint, oWord, oDoc
Set xlApp = CreateObject("excel.application")
With xlApp.Workbooks.Open("c:\test\test\test.xls")

.Close True
End With

Set xlApp = Nothing


you really only have to change the name of the file where you have the macro uploaded to.

here is the vba macro:

Sub Summarize2()

Dim Counter As Long
Dim fn As String, ws As Worksheet, wb As Workbook

Const MyDir As String = "c:\test\"

fn = Dir(MyDir & "*.xls")
If fn = "" Then Exit Sub
Set wb = Workbooks.Add
Do While fn <> ""
With Workbooks.Open(MyDir & fn)
For Each ws In .Sheets
ws.Copy Before:=wb.Sheets(1)
wb.Sheets(1).Name = fn & "_" & ws.Name
.Close False
End With
fn = Dir
End Sub

You only need to change the folder where you want it to gather the worksheets from.

Report •

Ask Question