Need help writing a vb script that adds a row to the first line

[arlinda]

Hi,

I am new to this forum and scripting. I am tring to add a row to an excel file. The file contains 6 colums and the rows are populated with peoples First Name, Mid Init, Last name, birthdate, job type and date.

I am trying to add a row with Description of the colums (header)at the begining of the spreadsheet. I am not sure how to go about doing this?


Const xlShiftDown = -4121

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("c:\tele.csv")
Set objWorksheet = objWorkbook.Worksheets(tele)

int i = 1
int j = 1

Do Until objWorkSheet.Cells (i, 1) = ""
for j = 1 to 6
Set objRange = objWorksheet.Cells(i,1).EntireRow
objRange.Insert(xlShiftDown)
objWorkSheet.Cells(i, 1).Value = "First name"
Loop
objExcel.Quit

 

[HarleyQuinn]

If I'm honest the best (and by far the easiest) is to record a macro of yourself doing this in Excel and then using the code with your Excel objects in VB. There'll be some chaff in there that's unnecessary but that's easily weeded out.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[arlinda]

Ok I created a macro. Below is what it looks like how do I point it to my excel file so I can run it?

Sub mymacro1()
'
' mymacro1 Macro
'

'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "First Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Middel int"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Last name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Start Date"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Expiration Date"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Dept Num"
Range("G1").Select

End Sub

 

[SkipVought]

objWorksheet must be the object reference of each RANGE object...

[b]
with objWorksheet 
    .[/b]Rows("1:1").Insert Shift:=xlDown[b]
    .[/b]Range("A1").Value = "First Name"

[b]
end with[/b]

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[arlinda]

Hi,

Below is what I have. Please let me know what I am doing wrong. When I compile this script, I get a message saying Line 14
Char: 28
Error: Expected statement

1 Sub mymacro1()
2 '
3 ' mymacro1 Macro
4 ' Macro recorded 2/20/2008 by agnetha
5 '
6
7 '
8 Set objXL = CreateObject("Excel.Application")
9 Set objWB = objXL.WorkBooks.Open("C:\data\tele.csv")
10 Set objWS = objXL.ActiveWorkBook.WorkSheets("tele")
11
12
13 Rows("1:1").Select
14 Selection.Insert Shift:=xlDown
15 Range("A1").Select
16 ActiveCell.FormulaR1C1 = "First Name"
17 Range("B1").Select
18 ActiveCell.FormulaR1C1 = "Middel int"
19 Range("C1").Select
20 ActiveCell.FormulaR1C1 = "Last name"
21 Range("D1").Select
22 ActiveCell.FormulaR1C1 = "Start Date"
23 Range("E1").Select
24 ActiveCell.FormulaR1C1 = "Expiration Date"
25 Range("F1").Select
26 ActiveCell.FormulaR1C1 = "Dept Num"
27 Range("G1").Select
28
29 objWB.Close
30 objXL.Quit
31
32 End Sub

 

[SkipVought]

REPEATING...

"objWorksheet must be the object reference of each RANGE object..."

then followed a CORRECTED example...

Helloooooooooooo

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[arlinda]

Sorry about the previous script. I forgot to add the reference. But I am still getting the following error
Line 14
Char: 34
Error: Expected statement




1 Sub mymacro1()
2 '
3 ' mymacro1 Macro
4 ' Macro recorded 2/20/2008 by agnetha
5 '
6
7 '
8 Set objXL = CreateObject("Excel.Application")
9 Set objWB = objXL.WorkBooks.Open("C:\data\teletick.csv")
10 Set objWS = objXL.ActiveWorkBook.WorkSheets("teletick")
11
12
13 objWB.Rows("1:1").Select
14 objWB.Selection.Insert Shift:=xlDown
15 objWB.Rows.Range("A1").Select
16 objWB.Rows.ActiveCell.FormulaR1C1 = "First Name"
17 objWB.Rows.Range("B1").Select
18 objWB.ActiveCell.FormulaR1C1 = "Middel int"
19 objWB.Range("C1").Select
20 objWB.ActiveCell.FormulaR1C1 = "Last name"
21 objWB.Range("D1").Select
22 objWB.ActiveCell.FormulaR1C1 = "Start Date"
23 objWB.Range("E1").Select
24 objWB.ActiveCell.FormulaR1C1 = "Expiration Date"
25 objWB.Range("F1").Select
26 objWB.ActiveCell.FormulaR1C1 = "Dept Num"
27 objWB.Range("G1").Select
28 objWB.ActiveCell.FormulaR1C1 = "Employee Numb"
29 objWB.Range("H1").Select
30 objWB.Close
31 objXL.Quit
32
33 End Sub

 

[SkipVought]

Did you NOT see the posted example?

Where is Select in that example???

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[arlinda]

Ok, what am I doing wrong? Sorry about earlier.

Sub mymacro1()
'
' mymacro1 Macro
'
'

'
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("C:\data\tele.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("tele")


objWB.Rows("1:1").Insert Shift:=xlDown
objWB.Range("A1").Value = "First Name"
objWB.Range("B1").Value = "Middel int"
objWB.Range("C1").value = "Last name"
objWB.Range("D1").value = "Start Date"
objWB.Range("E1").value = "Expiration Date"
objWB.Range("F1").value = "Dept Num"
objWB.Range("G1").value = "Employee Numb"
objWB.Close
objXL.Quit

End Sub

 

[SkipVought]

Please READ and APPLY...

"The [red]WORKSHEET[/red] must be the object reference of each RANGE object..."

You changed your Object Variables and NOW you are using the WORKBOOK object!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[arlinda]

Hi,
This is my script

Sub mymacro1()
'
' mymacro1 Macro
' Macro recorded 2/20/2008 by agnetha
'

'
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("C:\data\teletick.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("teletick")

with objWorksheet
.Rows("1:1").Insert Shift:=xlDown // Expected statement error (is this line of code correct or should it be "Worksheets("tele").Rows("1:1").Insert Shift:=xlDown ")

.Range("A1").Value = "First Name"
.Range("B1").Value = "Middel int"
.Range("C1").value = "Last name"
.Range("D1").value = "Start Date"
.Range("E1").value = "Expiration Date"
.Range("F1").value = "Dept Num"
.Range("G1").value = "Employee Numb"
end with
objWB.Close
objXL.Quit

End Sub

 

[SkipVought]

Hi,

These should be the SAME object

Set objWS = objXL.ActiveWorkBook.WorkSheets("teletick")

with [red][b]objWS[/b][/red]

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[arlinda]

When I compile the script it gives me an expected statement error

with objWS

objWS.Rows("1:1").Insert Shift:=xlDown ///this line is giving me an expected statement error. I am not sure why?

Thank you

 

[JoeAtWork]

You can't use constants like xlDown in VBScript. You need to give it's numeric equivalent.

Anticipating your next question: the number representing xlDown can be found with the Object Browser in Excel VBA, or by typing "?xlDown" in the Excel VBA's Immediate window. There's also Google.

 

[arlinda]

Hi,

I am not sure how this is going to work, but with some research on google this is what I came up with. When I compile this script, it doesn't do anything to my file. I am new to programing so your comments are helpful.


Sub mymacro1()
'
' mymacro1 Macro
' Macro
'

'
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("C:\data\telet.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("tele")

With objWS

objWS.Rows("1:1").Insert
objWS.Application.Selection.End(xlDown).Select()
objWS.Range("A1").Value = "First Name"
'objWS.Range("A1").end(xlDown)
objWS.Range("B1").Value = "Middel int"
objWS.Range("C1").value = "Last name"

end with
objWB.Close
objXL.Quit

End Sub

 

[genomon]

Hey arlinda!
All the gurus hang in the VBScript forum:

http://www.tek-tips.com/threadminder.cfm?pid=329

This is Visual Basic 5 & 6.

Might have better luck there...

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[gmmastros]

arlinda,

I would suggest that you save the file after you modify it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arlinda]

Thank you.