Excel, add row, shift all values down - Please Help!!!

I am taking a .txt file delimiting it and exporting to excel. I need to add a row (column names) from a different spreadsheet to where my exported spreadsheet exists.  
thanks in advance
strExcelPath = "C:\myfiles\header.xls"
 
Const ForReading = 1
Const ForWriting = 2
 
'Added to save changes without being prompted by Excel
Const xlSaveChanges = 1
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
Set objFile = objFSO.OpenTextFile("C:\myfiles\test.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close
 
strContents = Replace(strContents, "|", vbTab)
 
Set objFile = objFSO.OpenTextFile("C:\myfiles\test.txt", ForWriting)
objFile.Write strContents
objFile.Close
 
Set objExcel = CreateObject("Excel.Application")
'Add row here???
'Shift all other values down
Set objWorkbook = objExcel.Workbooks.Open("C:\myfiles\test.txt")
objExcel.ActiveWorkbook.cells.addrow("1,1")
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close xlSaveChanges
objExcel.Application.Quit
WScript.Echo "The Upload is Complete"

Open in new window

gabulishAsked:
Who is Participating?
 
DanRollinsConnect With a Mentor Commented:
One option would be to insert the line when you write the file (rather than after importing into Excel).  That might look something like this:
Set objFile = objFSO.OpenTextFile("C:\myfiles\test.txt", ForReading)
strContents = objFile.ReadAll
objFile.Close
 
strContents = Replace(strContents, "|", vbTab)
 
Set objFile = objFSO.OpenTextFile("C:\myfiles\test.txt", ForWriting)
 
' REM added this line --------------------------
objFile.Write "clm1" +vbTab+ "clm2" +vbTab+ "clm3" +vbCr
 
objFile.Write strContents
objFile.Close

Open in new window

0
 
byundtCommented:
Instead of:
objExcel.ActiveWorkbook.cells.addrow("1,1")


Try:
objExcel.ActiveWorkbook.ActiveWorksheet.Rows(1).Insert

0
 
gabulishAuthor Commented:
DanRollins,

I like your approach. Could I open another WorkBook and grab a row from a Worksheet and paste it into the original Workbook, Worksheet?

thanks much,

gabulish
0
 
DanRollinsCommented:
I suppose so.  You would need to access functions of your objExcel object and extract the desired text from the specidied cells.  I have little experience with the Excel object model.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.