| || |
TextToColumns function seemingly does nothing...?
I'm a little new to automation and typically overcome these sort of newbie issues on my own, but I need a pointer on this one if someone can help.
Using TestPartner, I'm simply trying to write a function that will open a CSV file in Excel, execute the TextToColumns function, then save the file elsewhere. Upon executing the function, it runs and finishes without error. However, when I go to see the results, the file it saved off appears not to be modified at all. That is, it's as if the TextToColumns function was not even executed; the data is still in one big comma-delimited line.
If I instead take the TextToColumns line that I wrote in TestParter and paste it into an Excel macro, with a few syntactical changes, the code executes and turns everything to columns as expected. So, I don't understand why the same results aren't seen via TestPartner.
I appreciate any assistance. Thanks!
<font class="small">Code:</font><hr /><pre>
Public Function FnConvertTextToColumns(ByVal strFullFilePath As String, ByVal strFullFilePathToSaveFile As String, Optional ByVal intWorksheet As Integer = 1)
'Initialize Excel Object
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
'Set visibility to false
objExcel.Visible = False
'Open a workbook
Dim objWorkbook As Object
Set objWorkbook = objExcel.Workbooks.Open(strFullFilePath)
Dim objWorksheet As Object
Set objWorksheet = objWorkbook.Worksheets(intWorksheet)
objWorksheet.Columns("A:A").TextToColumns Destination:=objWorksheet.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, TrailingMinusNumbers:=True
'Turn off alerts so we can overwrite existing files without getting prompted.
objExcel.DisplayAlerts = False
'Do the Save
'Close the workbook. If we forget to do this, TestParter keeps a lock on it.
Re: TextToColumns function seemingly does nothing...?
I don't know much about the Excel object model but have you tried looking at the return code for the TextToColumns method? Maybe it does execute but it reports an error which your program is ignoring?