Text to Columns in VBA returning "Object does not support this method" error

[jandr272]

Text to Columns in VBA returning "Object does not support this method" error

I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).

Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.screenupdating = False
excelApp.Visible = False
Set excelWB = excelApp.workbooks.Open("Z:\Data\BasicSMData.xlsx")
Set excelWS = excelWB.worksheets("ExportData")
    excelApp.Columns("D:D").Select
    excelApp.Selection.TextToColumns Destination:=excelApp.Range("D:D"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
excelWB.Close SaveChanges:=True
[COLOR=red][/COLOR]

I'm no longer getting an error, but it isn't actually modifying the spreadsheet...

 

[Rx_]

Re: Text to Columns in VBA returning "Object does not support this method" error

Presuming there is an Option Explicit up on top of your module.
In your code module - Tools References - do you have Excel checked?
If so, use Dim excelApp As Excel.Application
If Late Binding must be used later, develop with this first, then go back to OBJECT
Then use the menu Debug - Compile to check out the code.
excelWB is not declared as an Excel.Workbook

Without an OnError statement - can you set excelApp.Visible to True
then step through the code a line at a time?

 

[jandr272]

Re: Text to Columns in VBA returning "Object does not support this method" error

You are the man (or woman)! Checking Excel and using Dim excelAPP As Excel.Application worked. Any idea why this worked in lieu of the other way? The other way would delete rows or select a column without a problem it was just the "text to columns" operation that wouldn't work.

As aggravating as VBA can be, this forum is making it a lot of fun to learn!