VBA Code doesn't work on all excel sheets

Question

Setting:

Using VBA in Access
Exporting four tables as four sheets in one new excel file with DoCmd.TransferSpreadsheet acExport
modify the exported file withing vba code in access

What I want to do:

There are four sheets in the excel file. In every sheet on all filled columns the "textToColumn" function should be applied.

The Problem:

Making the first row bold works on every sheet. "textToColumn" functions only work on the first sheet.

My Code:

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open("C:\Users\jfrick\Documents\BPM Access\Revenue Evolution GFS.xlsx")

With xlApp
        .Application.Cells.Select
        .Application.Selection.ClearFormats
        .Application.Rows("1:1").Select
        .Application.Selection.Font.Bold = True
        .Application.Cells.Select
        .Application.Selection.RowHeight = 12.75
        .Application.Selection.Columns.AutoFit

        .Application.Range("E:E").TextToColumns
        .Application.Range("F:F").TextToColumns             
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
End With

I also tried using the following code snippet but this also only changes the columns in the first sheet:

Dim ws As Worksheet

For Each ws In xlApp.Worksheets 'also tried xlSheet.Worksheets but same result
    ws.Application.Range("E:E").TextToColumns
    ws.Application.Range("F:F").TextToColumns
Next ws

--> How can i apply the "textToColumn" function on the columns in every sheet?

would you mind to open Task Manager and tell me, how many Excel processes do you have after running your macro? — MarcinSzaleniec

Erik A Erik A · Accepted Answer · 2017-12-11T10:55:34

You're almost there, just a simple misconception:

Change this:

For Each ws In xlApp.Worksheets 'also tried xlSheet.Worksheets but same result
    ws.Application.Range("E:E").TextToColumns
    ws.Application.Range("F:F").TextToColumns
Next ws

To this:

For Each ws In xlApp.Worksheets
    ws.Range("E:E").TextToColumns
    ws.Range("F:F").TextToColumns
Next ws

Since you're referring to ws.Application.Range in your attempt, you're calling .Range on the application object and not the worksheet object, which refers to the active worksheet, not ws

VBA Code doesn't work on all excel sheets

1 Answers