VBA - Text to columns to format and Preparing Worksheet (press esc to cancel)

[JAndy_CO]

I have searched the forums here and cannot find a solution that makes sense to me, so here goes-

I have data from a query. It is in table format and I need to format two columns. The first is a list of stock numbers. Some contain only numbers, some have alpha characters at the end. i.e.
[TABLE="width: 100"]
<tbody>[TR]
[TD]STOCK NUM.[/TD]
[/TR]
[TR]
[TD]838373[/TD]
[/TR]
[TR]
[TD]83836C[/TD]
[/TR]
[TR]
[TD]229373[/TD]
[/TR]
[TR]
[TD]87749A[/TD]
[/TR]
</tbody>[/TABLE]

There is also a column that has dates but the are formatted like this: [TABLE="width: 100"]
<tbody>[TR]
[TD]DELIV. DATE[/TD]
[/TR]
[TR]
[TD]2018-05-19[/TD]
[/TR]
[TR]
[TD]2018-11-22[/TD]
[/TR]
</tbody>[/TABLE]
so Excel doesn't recognize date formatting which I need.

The table has +12,000 rows, and needs to be able to be refreshed and cleaned up with a macro.

The code I have written now seems to take a very long time to run outside of the query refresh part, and has some bugs which I will explain after sharing my code. This is my code:

Sub Refresh_Inventory_Fix_Stock_Num()
'
' Refresh_Inventory_Fix_Stock_Num Macro
'

' turn off auto calculations

    Application.Calculation = xlCalculationManual

' refresh workbook query connection
    Sheets("Current Inventory").Select
    ActiveWorkbook.RefreshAll
    
' select range for stock number list
    Range("Table_Query_from_Inventory[StockNumber]").Select
    
' run text to columns on the stock number column to correct formatting
    Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        
' select range for delivery date list
    Sheets("Current Inventory").Select
    Range("Table_Query_from_Inventory[DeliveryDate]").Select
    
' run text to columns on the delivery date column to correct date formatting
    Selection.TextToColumns Destination:=Range("L2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        
'turn on auto calculations
    Application.Calculation = xlCalculationAutomatic
        
'Select dashboard sheet where macro button exists
    Sheets("EOD Report").Select
End Sub

Text to columns is the only way I know to format all these imported values quickly, but the end user will be on a thin client (cloud computing) so they will not have much processing power. (it takes about 35 seconds to run on my laptop, but 5-7 minutes on the thin clients. Is there another way to format the Stock Number and Delivery Date columns that is quicker/more efficient?

Also, (and this is the main issue that is halting implementation) I can run this macro dozens of times in a row on my laptop and it runs perfectly. HOWEVER, on the thin clients, the macro runs through fine the first time in about 5 minutes, but if I attempt to run it again before closing the file completely, it gets stuck in the status bar at "Preparing Worksheet (Press ESC to cancel)". I've let it sit several times for +60 mins. I've tested waiting 5 minutes to run the macro again, and I've tested waiting an hour+ to run it. It gets stuck at "Preparing Worksheet" every time the macro runs the second time. I don't have this problem on my laptop or any other actual PC that I've tested it on. Only the thin clients (again cloud computing). Do you have an idea of why this is happening and how I might be able to overcome it?

Thank you for helping me! (Also this is my first forum post so I hope everything is kosher.)

 

[JAndy_CO]

Bump. Please Help!