Text to Column in SQL Query Table

[Nugget]

Hello all,
I am trying to format some data in a Table.

The Column in question contains a part number and name.
"Data[[#Headers],[Part]]" which is formatted like "124578 Part1"

In the macro, I have split this delimited data at the space, and it works fine.
"B1"="124578" and "C1"="Part1"

The issue is when I call this sub macro from another macro. Instead of returning the two separate columns, it returns the original column and the second split column. Regardless if I have "Application.DisplayAlerts" on or not.
"B1"="124578 Part1" and "C1"="Part1"

Where can I track down where this is happening at?

 

[Richard Schollar]

Hi

Please post your code - it's very difficult to help otherwise

 

[Nugget]

Sub RefreshData()
'
' 04/21/2011
'
'Application.ScreenUpdating = False
Data1 = Sheets("SetUpSheet").Range("B2").Value
Data1_QRY = "Select Top 100 * from Database.Object" & Chr(13) & "" & Chr(10) & "Where [XXXXX] = " & Data1 & "" & Chr(13) & "" & Chr(10) & "Order by [Date_Time] desc"
Data1_CONN = XXXXX


    Dim wSheet As Worksheet
        On Error Resume Next
        Set wSheet = Worksheets("Data")
            If wSheet Is Nothing Then
                Worksheets.Add(After:=Worksheets("SetUpSheet")).Name = "Data"
            Else
                
            End If
        On Error GoTo 0


Sheets("Data").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Data1_CONN, Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Data1_QRY
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Data"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Connections("Connection").Refresh
    Sheets("Data").Select
'Application.ScreenUpdating = True
End Sub


Sub FormatData()
'
' Macro4 Macro
'

'
    Sheets("Data").Select
    Columns("C:C").Select
    Selection.NumberFormat = "mm/dd/yyyy hh:mm:ss"
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").Select
    Application.DisplayAlerts = False
    Selection.TextToColumns Destination:=Range("Data[[#Headers],[Column]]"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
        :=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other _
        :=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:= _
        True
    Application.DisplayAlerts = True
    Columns("D:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:AI").Select
    Selection.NumberFormat = "0.000"
    Selection.ColumnWidth = 8

    Range("Data[[#Headers],[Index]]").Select
End Sub