VBS & Excel: Copy single field to new field for all records

[eao]

I want to copy the value of cell A1 into column F for each record that exists in Sheet1. I have found some code that allows me to open the workbook I want:

Option Explicit
Const vbNormal = 1         ' window style

DIM objXL, objWb, objR, objTab     ' Excel object variables
DIM Text, tmp, i, j, file, name

' here you may set the name of the file to be imported 
file = "foo.txt"    ' must be located in the script folder

' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

' set the Excel window properties (not absolutely necessary)
objXL.WindowState = vbNormal ' Normal
objXL.Visible = true         ' do not show window

' Create new Workbook (needed for import the CSV file=
Set objWb = objXl.WorkBooks.Add 

' Get the first loaded worksheet object of the current workbook
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)

I also will want to delete row 1 once the copy is complete. Can anyone point me in the right direction? Thanks.

 

[eao]

This should put the string "foo" in cell A1, correct?

ObjXL.ActiveSheet.Cells(1,1).Value = "foo"

So could I do something like this to set one cell equal to the value of another?

ObjXL.ActiveSheet.Cells(6,1).Value = ObjXL.ActiveSheet.Cells(1,1).Value

How would I do that for each row?

 

[eao]

Maybe I could find the number of rows, and then loop through and copy the cell to each row like this:

DIM iRowNumber
set xlSht = xlApp.activesheet

iRowNumber=1
while xlSht.Cells(iRowNumber, 1) <> ""
iRowNumber = iRowNumber + 1
wend

while iRowNumber > 0
ObjXL.ActiveSheet.Cells(6,iRowNumber).Value = ObjXL.ActiveSheet.Cells(1,1).Value
iRowNumber = iRowNumber -1
wend

Does anyone think that's got a chance?

 

[tsuji]

Hello eao,

What you have found by yourself is making a good lot of sense and certainly you can assign value to cell like that. Test it out with a testing workbook.

regards - tsuji

 

[eao]

Thanks tsuji,

Sometimes I just need think out loud to solve a problem.

 

[PHV]

You may test and copy in a single loop:
iRowNumber=1
While xlSht.Cells(iRowNumber, 1) <> ""
xlSht.Cells(6,iRowNumber).Value = xlSht.Cells(1,1).Value
iRowNumber = iRowNumber + 1
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244