Merging 2 excel file into single excel

[moonwalker]

I've been searching the forum but i cant find a specified help.

I have two xls files, a.xls and b.xls
i want to combine them into c.xls with worksheet a and b

can anyone give me some guide on this?
Im using vbscript

Set objXL = WScript.CreateObject ("Excel.Application")
Set objWb = objXl.WorkBooks.Add
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)
objWB.name = "a"
objWb.Activate

The above code open a need workbook with name "a". How to get a.xls
into the current workbook?
Appreciate ur help

 

[Dave Peterson]

Not tested...

But I think it sounds like you need something more like:

Set objXL = WScript.CreateObject ("Excel.Application")

Set objWbA = objXl.WorkBooks.open("C:\a.xls")
Set objWbB = objXl.WorkBooks.open("C:\b.xls")

objwba.worksheets(1).copy _
objwbb.worksheets(1)

=======
Is there a reason you're not using VBA?

 

[moonwalker]

=======
Is there a reason you're not using VBA?

actually its a long story.
im asked to write a vb script to get all the csv files in the directory
to merge into a single excel file with different worksheets..i actually
done that using the text import wizard in the vbscript but im having
problem with the carriage return as what i posted in the other
thread...

so i wrote another function to clean up the carriage return..but there
are still some formatting error..

so what im trying to doing now is using the vbscript to open up the csv
files and save them individually into separate excel files...then
combine them together into a single excel file without using the text
import wizard

 

[moonwalker]

Hey,

I tried the code snippet and its working perfectly.

I manage to merge 2 csv files into one single excel file.

However there is still a minor problem.
The excel file i created has Sheet1, 2, 3 and 2 other sheets created
according to the csv file name.

Whats the syntax for deleting the unused sheets and i would like to
know about renaming the sheet name.

I have manage to select the first 2 rows and make them bold.
objXL.Range("A1:R2").Select 'Select first 2 rows
objXL.Selection.Font.Bold = True 'Bold first 2 rows

How can i Adjust the Column Width?
i tried objXL.AdjustColumnWidth = True
but it doesnt have this method

Appreciate your help.

 

[Dave Peterson]

There is no single line to delete unused sheets.

You have to check. One way is to look at the used range and see if it's just
A1. Then check to see if A1 is empty. If both those things are true, then the
worksheet is _probably_ not used. But you never know--there could be something
that's being used that doesn't fit either of those rules.

Still untested and I don't use enough VBS to recognize problems just by looking!

dim objWks
dim objXL
dim objWBA

Set objXL = WScript.CreateObject ("Excel.Application")
set objWBA = objxl.workbooks.open("C:\a.xls")

for each objwks in objwba.worksheets
if objwks.usedrange.address = "$A$1" then
if objwks.range("a1").formula = "" then
if objWBA.sheets.count > 1 then
objxl.displayalerts = false
objwks.delete
objxl.displayalerts = true
end if
end if
end if
next objwks

======
I would still use a worksheet object--not the application.

objXL.Range("A1:R2").Select 'Select first 2 rows
objXL.Selection.Font.Bold = True

becomes
objWKS.range("a1:R2").font.bold = true
(after you set objwks to something nice.)

======
and
objWks.name = "NewNameHere"
(not one ounce of validation here, though.)

======
objwks.range("a1").entirecolumn.autofit
or
objwks.range("a1:x1").entirecolumn.columnwidth = 23

======
Some place in your code, you may want to use:

set objWks = objXL.Activesheet
or
set objWks = objWBA.Activesheet
or
set objWks = objWBA.worksheets(1)
or
set objWks = objWBA.worksheets("Sheet1")

 

[moonwalker]

i guess im done with my scripts...

im able to combine all csv files in a folder into a single excel
spreadsheet..and do some renaming to the worksheets

Thanks Dave
Appreciate your help