import .csv to excel and modify column from numeric to text 1

[ShadowFox333]

I want to import a .csv file into an excel worksheet
and then modify one of the columns from numeric to text.
The code below does opens excel and inputs the .csv file to a worksheet. I can't figure out how to modify the column property. TIA.
Bob

Option Explicit
Const vbNormal = 1

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

Title = "WSH sample - by G. Born"

file = "00130.csv"
name = "00130"

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

objXL.WindowState = vbNormal ' Normal
objXL.Height = 300 ' height
objXL.Width = 400 ' width
objXL.Left = 40 ' X-Position
objXL.Top = 20 ' Y-Position
objXL.Visible = true ' show window

' Load the Excel file from the script's folder
Set objWb = objXl.WorkBooks.Open(GetPath+file)

' Get the loaded worksheet object
Set objWb = objXL.ActiveWorkBook.WorkSheets("00130")
objWb.Activate ' not absolutely necessary (for CSV)

WScript.Echo "worksheet imported"


objXl.Quit()
Set objXL = Nothing

WScript.Quit()

Function GetPath
' Retrieve the script path
DIM path
path = WScript.ScriptFullName ' Script name
GetPath = Left(path, InstrRev(path, "\"))
End Function

 

[PHV]

Something like this ?
objWb.Columns("E:E").NumberFormat = "@"
or
objWb.Columns(5).NumberFormat = "@"

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

 

[ShadowFox333]

PHV Below is my latest version and it does all I want except
I need to change the save as window to xls to save the worksheet as a native .xls and I dont know what the control is. Can you tell me where to find the control names list for things like columns and cells and stuff.
thanks Bob

Option Explicit
Const vbNormal = 1

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

' Title = "WSH sample - by G. Born"

file = "00130.csv"
name = "00130"
savename = "c:\bob\vbscript\00130.xls"
' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

objXL.WindowState = vbNormal ' Normal
objXL.Height = 300 ' height
objXL.Width = 400 ' width
objXL.Left = 40 ' X-Position
objXL.Top = 20 ' Y-Position
objXL.Visible = true ' show window

' Load the Excel file from the script's folder
Set objWb = objXl.WorkBooks.Open(GetPath+file)

' Get the loaded worksheet object
Set objWb = objXL.ActiveWorkBook.WorkSheets("00130")
objWb.Activate ' not absolutely necessary (for CSV)

'WScript.Echo "worksheet imported"

objxl.columns("l:l").numberformat="@"

OBJXL.DISPLAYALERTS = fALSE
wscript.echo savename
objxl.activeworkbook.saveas savename
objXl.Quit()

Set objXL = Nothing

WScript.Quit()

'##########################
Function GetPath
' Retrieve the script path
DIM path
path = WScript.ScriptFullName ' Script name
GetPath = Left(path, InstrRev(path, "\"))
End Function
'*************************************************
'*** End -> WSH-VBScript ***
'*************************************************

 

[PHV]

Try this:
objxl.ActiveWorkbook.SaveAs savename, &HFFFFEFD1 ' xlWorkbookNormal
Another possible values:
35 'xlExcel4Workbook
43 'xlExcel9795
To discover the Excel's model object, go to the VBE (Visual Basic Editor : Alt-F11) and open the Object browser window (F2).
If during MS-Office installation the VBA help files was choosen, then you may use the F1 key.

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

 

[ShadowFox333]

PHV, Thanks workd like a champ - Bob