text file to excel

[heavydee]

Hello scripting gurus,

Who on earth can help me with a vbs code to copy 11 log (txt) files into excel

I have 11 text files that i need to read into excel everyday. This files are comma delimited, This i should be able to do with vbs without opening the text files one for one into excel.
All 11 text files has the same format and could all be placed in one worksheet in excel
Thanks in advance

[ALL]

Try playing around with this:

<script language="vbscript">

'START CONFIG
Dim strSaveLocation
strSaveLocation = "fullpath\path_to_save_excel_document.xls"

Dim aryFilesToOpen(2) 'You must change this number to however many files to open (remember to add 0 as one of them)
aryFilesToOpen(0) = "fullpath_to_book\Book1.txt"
aryFilesToOpen(1) = "fullpath_to_book\Book2.txt"
aryFilesToOpen(2) = "fullpath_to_book\Book3.txt"
'END CONFIG

'Create Excel Object
Set objExcel = CreateObject("Excel.Application")
'Adds an inital workbook
set objMasterWB = objExcel.workbooks.add()

'Removes all sheets but 1 beause you must have atleast 1 active sheet
Do While objMasterWB.Sheets.Count - 1
	objMasterWB.Sheets(1).delete
Loop

Dim i
'Loops through the array
For i = 0 to UBound(aryFilesToOpen)
	'Opens file as new workbook
	Set objTmpWB = objExcel.workbooks.open(aryFilesToOpen(i))
	'Selects sheet 1
	objTmpWB.Sheets(1).Select
	'Copys Sheet 1 to master workbook
	objTmpWB.Sheets(1).Copy(objMasterWB.Sheets(1))
	'Close Book
	objTmpWB.Close(false)
Next

'Deletes a trailing sheet
objMasterWB.Sheets(objMasterWB.Sheets.Count).delete

'Saves file, you can specify somewhere here to always override without prompting
objMasterWB.SaveAs(strSaveLocation)
'Close Book
objMasterWB.Close(false)
'Close Excel
objExcel.Quit()
'Remove Process
set objExcel = nothing
</script>

And Yes i did test and it does work!

[heavydee]

Perhalps i should just accept the fact that ill never be good at scripting let alone writing complex codes. In retrospect your code looks easy and it gets the job done. Thanks a lot.

How do i get to learn scripting ? I dont wanna give up. Where is the starting point for a dummy like me

[ALL]

My first language was Visual Basic for Applications (VBA) which technically wasnt a language but a macro language. I learnt the basics of variables and syntax then moved on to VB6 and then VB.NET then to PERL then PHP and then JavaScript. So in a sense I am a language hopper.

Where to start.... hmmm.... VBA is a good place to start or VBS is also a good place. Just start somewhere by taking a project you want to do and then go around online and look at code samples of how other people did it. When you have a semi-working model walk through it and try and figure out how it did it. Analize every little piece of code and figure out if it is necessary, and why it's there.

That's kinda how I did it. But i started when I was 15