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

[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?
Below is my code.
When I compile this script, it doesn't do anything to my file.

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("B1").Value = "Middel int"
objWS.Range("C1").value = "Last name"
objWS.Range("D1").value = "Start Date"
objWS.Range("E1").value = "Expiration Date"
objWs.Range("F1").value = "Dept Num"

end with
objWB.Close
objXL.Quit

End Sub

 

[tsuji]

You may be new to these all. The constance is all the more that you need to listen carefully what the exact advice you are given, particularly, in the vb forum you originally posted to.

Here is a minimal revision. Look at it carefully to detect the difference.
[tt]
Sub mymacro1()
'
' mymacro1 Macro
' Macro
'

'

[red]const xlDown=&HFFFFEFE7[/red]

Set objXL = CreateObject("Excel.Application")
[blue]'uncomment this if you want to see the action & debug
'objXL.visible=true[/blue]
Set objWB = objXL.WorkBooks.Open("C:\data\telet.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("tele")

With [red]objWS[/red]
'[red]no more objWS below[/red]
.Rows("1:1").Insert
.Application.Selection.End(xlDown).Select()
.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"

end With
[blue]'You do all those and close without saving?
'uncomment if you don't need the prompt
'objXL.DisplayAlerts=false
objXL.save[/blue]
objWB.Close
objXL.Quit

End Sub
[/tt]
You dim all those variables to make them local to the sub, otherwise all become global, not good if not with some reason.

 

[tsuji]

Amendment
This corresponding line should be read like this, my bad.
[tt] [red]'[/red]objXL.save
[red]objWB.save[/red]
[/tt]

 

[arlinda]

Hi,

Thank you for your comments. I did make the changes to the script but it doesn't do anything to my spreadsheet. Please advice.


Sub mymacro1()
'
' mymacro1 Macro
' Macro recorded
'

'
const xlDown=&HFFFFEFE7

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

With objWS

.Rows("1:1").Insert
.Application.Selection.End(xlDown).Select()
.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"

end with
objXL.DisplayAlerts=false
objWB.save
objWB.Close
objXL.Quit

End Sub

 

[tsuji]

I am not sure you even know how to run a vbs?!

[1] Save this to a file say d:\test\abc.vbs.
[tt]
'started here save to d:\test\abc.vbs for instance
[blue]call myMacro()[/blue]

Sub mymacro1()
'etc etc same lines you now have
End Sub
[/tt]
[2] Go to command prompt, cd to d:\test. You see the highlighted part as prompt. Then type the remaining characters.
[tt]
[highlight]d:\test>[/highlight]wscript.exe abc.vbs
[/tt]
Boring stuff, is it not?!

 

[arlinda]

Hi,

I missed the call function. Thank you for your help. The script work. Any advice on how to add the current date to a new colum called (Modification Date).

Thank you very much