how to Copy data from two files in one excel file?

[sanjay_bjamkar]

I have 2 files with same name & extension *.txt & *.hdr
I want to create New excel(*.xls) file with same name & copy data from both files above in excel file & in one sheet.

Is anybody know the solution of this?

[Carbon Dudeoxide]

You want to do this.....with a batch file?

[sanjay_bjamkar]

Yes,

Is it Possible?

[Carbon Dudeoxide]

Doubt it. Why?

[BatchFileCommand]

The solution is very easy, just copy the files and save them as .xls .
I think making a batch file just to do that is kind of useless unless you going to do more operations like that.

[Sidewinder]

The solution is very easy, just copy the files and save them as .xls .

If only it were that easy. You cannot turn files into proprietary file formats by copying them and changing the extensions.

Sanjay, I thought it was explained that you need to do this either within Excel or write an external script that can interact with the Excel application (not batch code). If you would write up the specs again, I'm sure we can help. Try to be crystal clear on where all the data from the files go. It was not clear in your other thread especially when you mentioned date and time.

 

[sanjay_bjamkar]

Happy New Year Sidewinder.

I will try to explain what exactly I want.

I have some trend files in *.hst format which will generate everyday with that day's time & date stamp in file name & I have one tool which covert these *.Hst files in 2 text files with extensions *.txt & *.hdr.

        *.hdr file contains only headers of that data.
exa.
3
Header1
Header2
Header3
In above exa. 3 means no. of parameters & Header 1, 2, 3 are headers of data.

*.txt file contains all data of that whole day with same 3 parameters.

exa.

Date        Time            Header1 Data                Header2 Data            Header3 Data

I want to combine these 2 files in single excel file because again I want to use this excel file for report generation.

I want to copy headers Header1, Header2, Header3 in first row & Column C, D & E respectively. In column A & B, I want to write 'Date' & 'Time' respectively because my *.txt file contain Date & time but header file not contain header for Date & Time.
Then I want to copy all Data from *.txt file to excel file below respective header.

I want to do this with a single click that's why I am trying to do this with run the batch file.

You are given me VB Script but by using this we can create blank excel file. but by using below code we can replace *.txt file in *.xls.

Code:
set dr=%systemdrive%\yourfolderpath&CLS
for /f "tokens=1*" %%a in ('dir /a %dr% /b') do (
       call copy "%dr%\%%a%%b"
       rename * *.EXL
)

But I want to copy headers in first row of this *.xls file.


Is It Possible?
Waiting for Ur reply.

[Sidewinder]

Please confine each of your topics to a single thread. You currently have more than three threads all requesting the same solution. Not only is this confusing to the members, but to anyone trying to follow along at home.

You are given me VB Script but by using this we can create blank excel file. but by using below code we can replace *.txt file in *.xls.

set dr=%systemdrive%\yourfolderpath&CLS
for /f "tokens=1*" %%a in ('dir /a %dr% /b') do (
       call copy "%dr%\%%a%%b"
       rename * *.EXL
)

The solution is very easy, just copy the files and save them as .xls .

If only it were that easy. You cannot turn files into proprietary file formats by copying them and changing the extensions.

The solution is not confined to VBScript. However, VBScript is installed with Windows, making it accessible to the most users. In any case you need to create an instance of Excel and let Excel handle the format of the resulting workbook.

Code: [Select]

Const F_FOLDER = "c:\temp"
Const ForReading = 1

Set fso = CreateObject("Scripting.FileSystemObject")
Set xlApp = CreateObject("Excel.Application")

Set f = fso.GetFolder(F_FOLDER)
Set fc = f.Files

For Each fs In fc
If LCase(fso.GetExtensionName(fs)) = "txt" Then
Set xlBook = xlApp.Workbooks.Add()
Set xlSheet = xlBook.Worksheets(1)

Set h = fso.OpenTextFile(F_FOLDER & "\" & fso.GetBaseName(fs) & ".hdr", ForReading)
numRow = 1
numCol = 3
xlSheet.Cells(1,1).Value = "Date"
xlSheet.Cells(1,2).Value = "Time"
Do Until h.AtEndOfStream = True
strHdr = h.ReadLine()
If Not IsNumeric(strHdr)
xlSheet.Cells(numRow, numCol).Value = strHdr
numCol = numCol + 1
End If
Loop
h.Close

Set t = fso.OpenTextFile(F_FOLDER & "\" & fso.GetBaseName(fs) & ".txt", ForReading)
numRow = 2
Do Until t.AtEndOfStream = True
xlSheet.Cells(numRow, 1).Value = Date
xlSheet.Cells(numRow, 2).Value = Time
arrTxt = Split(t.ReadLine(), " ")               'space delimiter in txt file
numCol = 3
For Each txt in arrTxt
xlSheet.Cells(numRow, numCol).Value = txt
numCol = numCol + 1
End If
numRow = numRow + 1
Loop
t.Close
xlBook.SaveAs F_FOLDER & "\" & fso.GetBaseName(fs) & ".xls"
End If
Next
xlApp.Quit

Thank you for the warm New Year's greeting. As my New Year's gift to you, I give you the responsibility of testing the code. I didn't think it fair that I have all the fun.

Save the script with a vbs extension and run from the command prompt as wscript scriptname.vbs

Good luck. 

Notes: You never mention how the data in the txt files was arranged. The script uses a space as the delimiter. This can be changed.

Excel workbooks have XLS extensions, not EXL

[sanjay_bjamkar]

Hi Sidewinder,

I try your code but it is not working, it gives something error "Microsoft VBScript compilation error:Expected 'Then'".

Please check this, if there is any solution please reply.

The screen shot of error file attached, please see it.

Thanks

[attachment deleted by admin]