User Tag List

Results 1 to 2 of 2
  1. #1
    Junior Member
    Join Date
    Apr 2012
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    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)

    'Set worksheet
    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
    objWorkbook.SaveAs strFullFilePathToSaveFile

    'Close the workbook. If we forget to do this, TestParter keeps a lock on it.

    End Function
    </pre><hr />

  2. #2
    Advanced Member
    Join Date
    Jan 2002
    Detroit, Michigan
    Post Thanks / Like
    2 Post(s)
    0 Thread(s)

    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?



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 05:40 AM.

Copyright BetaSoft Inc.