Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

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

0
gabulish
Asked:
gabulish
  • 2
1 Solution
 
byundtCommented:
Instead of:
objExcel.ActiveWorkbook.cells.addrow("1,1")


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

0
 
DanRollinsCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now