Solved

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

Posted on 2007-11-15
4
741 Views
Last Modified: 2008-02-01
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
Comment
Question by:gabulish
  • 2
4 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 20300506
Instead of:
objExcel.ActiveWorkbook.cells.addrow("1,1")


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

0
 
LVL 49

Accepted Solution

by:
DanRollins earned 150 total points
ID: 20300567
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
 

Author Comment

by:gabulish
ID: 20300680
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 20301551
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question