So the following code works correctly with the exception of these two lines:
TFCDT=Arrayize("2,2,2,2,2,2",",")
oQ.TextFileColumnDataTypes = TFCDT
I am trying to import a csv and force the columns to be text so as to not lose the formatting.
In a macro the line comes up:
.TextFileColumnDataTypes = Array(2,2,2,2,2,2)
Can't quite seem to figure out how to do that in wbt.
TIA!
jerway
xlInsertDeleteCells = 1
xlDelimited = 1
xlTextQualifierDoubleQuote=1
cCSV = WorkFile
cXLS = StrReplace( StrUpper(cCSV), ".CSV",".XLS")
cText = StrCat("Transferring %cCSV%",@CRLF,"TO %cXLS%")
IF FileExist(cXLS) Then FileDelete(cXLS)
cURL = StrCat("TEXT;",Workfile)
oXL = ObjectOpen("Excel.Application")
oXL.Visible = @TRUE ;if testing have this set to @TRUE until you are sure
oXL.UserControl = @TRUE ;if testing have this set to @TRUE until you are sure
oXL.DisplayAlerts = @TRUE ;if testing have this set to @TRUE until you are sure
oXL.ScreenUpdating = @TRUE ;if testing have this set to @TRUE until you are sure
oXL.Workbooks.Add()
oWS = oXL.ActiveWorkbook.Worksheets("Sheet1")
oWS.Activate
oQ = oWS.QueryTables.Add(:: Connection=cURL,Destination=oWS.Range("A1"))
oQ.Name = "test"
oQ.FieldNames = @True
oQ.RowNumbers = @False
oQ.FillAdjacentFormulas = @False
oQ.PreserveFormatting = @True
oQ.RefreshOnFileOpen = @False
oQ.RefreshStyle = xlInsertDeleteCells
oQ.SavePassword = @False
oQ.SaveData = @True
oQ.AdjustColumnWidth = @True
oQ.RefreshPeriod = 0
oQ.TextFilePromptOnRefresh = @False
oQ.TextFilePlatform = 437
oQ.TextFileStartRow = 1
oQ.TextFileParseType = xlDelimited
oQ.TextFileTextQualifier = xlTextQualifierDoubleQuote
oQ.TextFileConsecutiveDelimiter = @False
oQ.TextFileTabDelimiter = @True
oQ.TextFileSemicolonDelimiter = @False
oQ.TextFileCommaDelimiter = @True
oQ.TextFileSpaceDelimiter = @False
TFCDT=Arrayize("2,2,2,2,2,2",",")
oQ.TextFileColumnDataTypes = TFCDT
oQ.TextFileTrailingMinusNumbers = @True
oQ.Refresh()
oXL.Visibile=@TRUE
oXL.ScreenUpdating=@TRUE
Wild guess but assuming you have a relatively new version of WinBatch, you might give this a try
TFCDT=Arrayize("2,2,2,2,2,2",",")
oQ.TextFileColumnDataTypes = ObjectType("array|i4", TFCDT)
Or if you have an older version of WinBatch maybe
TFCDT = ArrDimension(6)
ArrInitialize(TFCDT, 2)
oQ.TextFileColumnDataTypes = TFCDT
THANK YOU! Worked perfectly.