TextToColumns in Access module

[ino_mart]

All

I have Excel files with multiple worksheets which are created by some PHP-system. After opening the XLSX, the numbers seem to be in Text-format instead of number. In Excel, I can fix this by using the Text-to-Columns-function.

The problem is those XLSX-files needs to be imported in an Access-database by endusers. After opening it in Access, the code adds an extra column with a TREND-function into the XLSX. Next, the Access database puts over the data into SQL-tables.

The TREND-function fails as the numbers are in a Text-format, so I tried to use the TextToColumns in the Access VBA-script.

I tried code below in my Access 2007-project, but the column is not converted and stays in the Text-format.

Private Sub cmdButton_Click()
Dim xlApp As Object 'Excel.Application
Dim xlWrk As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet
 
Set xlApp = VBA.CreateObject("Excel.Application")      
Set xlWrk = xlApp.Workbooks.Open("test.xlsx")
Set xlSheet = xlWrk.worksheets(1)
 
xlApp.Visible = False
For intNumberSheets = 1 To xlApp.sheets.Count
    xlApp.sheets(intNumberSheets).Activate
 
        'Convert text to number
        xlSheet.Range("L:L").textToColumns Destination:=xlSheet.Range("L:L"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Next intNumberSheets
xlWrk.Save
xlWrk.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlWrk = Nothing
Set xlApp = Nothing
MsgBox ("done")
Exit Sub