0

I am trying to get my Text To Columns to work in my VBA script, but it's not working for me. I understand how it works in excel and even used the macro record to get it working, but when adding it to my script nothing happens. I even looked at online forums. What I'm doing is extracting data from one workbook to the other and the data that I am getting needs to be converted to Text To Columns once the data is transferred to the other workbook.

I have two separate Sub functions that I used below: Neither of them worked. One of them is from the macro record and the other I found online.

Code from Macro Record below:

Sub TextToCol()

    Selection.TextToColumns Destination:=Range("D3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
        :="" & Chr(10) & "", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
End Sub

Code from online forum below:

Sub TextToCol()    
    Selection.Range("D3:D8").TextToColumns _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Space:=True
End Sub

The code below is what I am using to connect to source workbook:

'Save Above Created New Workbook
sWorkbook.SaveAs Filename:="C:\Users\username\Desktop\fileName\Test.xlsx"
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
Set OpenBook = Application.Workbooks.Open(FileToOpen)

Below is the code to send data to the destination workbook:

Workbooks("Test.xlsx").Worksheets("Sheet1").Range("D" & counter).value = dataThatWasExtracted

What am I doing wrong?

alphanumEric
  • 89
  • 8
  • What happens when you run your code? – Tim Williams May 22 '22 at 23:48
  • When I run it the form is the same. It doesn't convert to Text To Columns – alphanumEric May 22 '22 at 23:49
  • I meant the data is the same. – alphanumEric May 22 '22 at 23:58
  • 2
    `Selection.Range("D3:D8")` may be incorrect - perhaps you mean `ActiveSheet.Range("D3:D8")` ? Would be useful to include a sample of the type of data you're working with. – Tim Williams May 23 '22 at 00:01
  • 1
    In the first example you have `Chr(10)` (vbLf) as the delimiter under `OtherChar` but `Other` needs to be `True` for that to have any effect. See: https://learn.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns#:~:text=True%20to%20have%20DataType%20be%20xlDelimited%20and%20to%20have%20the%20character%20specified%20by%20the%20OtherChar%20argument%20be%20a%20delimiter.%20The%20default%20value%20is%20False. – Tim Williams May 23 '22 at 00:05
  • Hey, Thx for the response. I did make the changes, but still not working? – alphanumEric May 23 '22 at 00:44
  • *`Selection.Range("D3:D8")` may be incorrect* as written by @TimWilliams. If `Selection` was `D3:D8`, the range returned by that line would be `G5:G10`. I doubt this is what you want. `Select` is a good thing to avoid. Use explicit references instead. Search this forum for "How to avoid using select in VBA" – Ron Rosenfeld May 23 '22 at 00:47
  • So how would the range return be G5:G10. ? – alphanumEric May 23 '22 at 00:49
  • I realized when I run the code it doesn't work, but when I leave the workbook open and just run the Sub TextToCol() function it works lol – alphanumEric May 23 '22 at 00:54
  • So it seems the ActiveSheet.Range("D3:D8) worked for me. I just need to figure out why it only works when I just run the TextToCol() once the data is there. – alphanumEric May 23 '22 at 00:57
  • *how would the range return be G5:G10.* See [MS Range.Range Property documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.range.range). – Ron Rosenfeld May 23 '22 at 11:34
  • 1
    *why it only works when I just run the `TextToCol()` once the data is there.* Possibly `ActiveSheet` does not refer to `Worksheets("Sheet1")`. If you search for the stackoverflow article I mentioned above in my very first comment, it includes advice and reasons for avoiding those kinds of references (eg `Active.Sheet`) also. – Ron Rosenfeld May 23 '22 at 11:37

0 Answers0