Search for string using VBscript and copy that column to another worksheet

Question

I have Book1.csv and Book2.xlsx. Book1.csv has many columns with data. Each column has unique title in first ROW. I need to find column with title “Processor Time” and copy all available data in this column to column1 in Book2.xlsx using VBscript. Please help.

Answer 1

You can use ADO to get the column:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Docs\;"
strcon = strcon & "Extended Properties=""Text;FMT=Delimited;HDR=Yes;IMEX=1"";"

cn.Open strcon

strSQL = "Select [Processor Time] From [Book1.csv]"

rs.Open strSQL, cn

MsgBox rs.GetString

You can use automation with Excel:

Set xl = CreateObject("Excel.Application")

You can write to Excel from a recordset with:

xl.Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

You can post back if you have problems putting the bits together.

EDIT re Comment

Try switching your code around a little:

Set xl = CreateObject("Excel.Application") 
xl.Visible = True 
Set objWorkbook1=xl.Workbooks.Open("C:\Docs\book2.xlsx") 

Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Docs\;" 
strcon = strcon & "Extended Properties=""Text;FMT=Delimited;HDR=Yes;IMEX=1"";" 

cn.Open strcon 
strSQL = "Select [Processor Time] From [Book1.csv]" 
rs.Open strSQL, cn 

objWorkbook1.Worksheets("Sheet1").Cells(2, 1).CopyFromRecordset rs