Splitting columns doesn't work with button

Question

For work, I have to pull various information from a USDA report. I have created a sub that will pull the information I need from a text file, into a worksheet "USDA Weekly." I created another sub, using the recorder, that uses text to column to split the information (via fixed width). The sub that does the splitting is the only sub that changes the information on the "USDA Weekly" sheet in any way. All other subs pull information from this sheet.

I have a button on another worksheet (in the same workbook) that runs all the subs I have created for the report I have to do, including the two subs mentioned. Now, when I run sub to split the information it works perfectly however when I click the button to run all the subs including the splitter it doesn't work.

I have run this in the debugger step by step numerous times, in an attempt to figure out why this is happening with no luck. I'm mostly confused as to why it the sub doesn't work when the button is clicked, yet works when run by itself. Any tips as to why this isn't working is appreciated.

Edit: clarification on how this doesn't work. When the splitting sub is run through the button, it does not split the column at all, as shown below. No errors, or anything pop-up.

Edit Edit: as per Mathieu Guindon (implict) suggestion I have modified formatUSDAWeekly to use a with statement to avoid implicit references.

Sub formatUSDAWeekly()
'this sub pulls information from strictly within the workbook

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook 'sets wb to the workbook that contains the code (i.e. this workbook)

Set ws = wb.Sheets("USDA Weekly")

With ws

    .Range("A:A").TextToColumns Destination:=.Range("A:A"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(39, 1), Array(50, 1), Array(52, 1), Array(61, 1), _
        Array(73, 1)), TrailingMinusNumbers:=True

End With

End Sub

This is the sub for the button

Sub start()

Call pullFrom610
Call formatUSDAWeekly
Call formatWIWorkbook
Call formatOSWorkbook

End Sub

This is the sub that pulls the information

Sub pullFrom610()
'this code was taken from Seamus Abshere
'on SO:https://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("USDA Weekly")

ws.Columns("A:F").ClearContents 'clears the previous information

With ws.QueryTables.Add(Connection:="URL;https://www.ams.usda.gov/mnreports/lm_pk610.txt", Destination:=ws.Range("A1"))

    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh

End With

End Sub

This is the sub that splits the information using text to column

Sub formatUSDAWeekly()
'this sub pulls information from strictly within the workbook

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook 'sets wb to the workbook that contains the code (i.e. this workbook)

Set ws = wb.Sheets("USDA Weekly")

ws.Range("A:A").TextToColumns Destination:=Range("A:A"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(39, 1), Array(50, 1), Array(52, 1), Array(61, 1), _
        Array(73, 1)), TrailingMinusNumbers:=True

End Sub

`Destination:=ws.Range("A:A")` you need to be explicit abut *every* range you use, or it defaults to the activesheet. That can cause problems once you start to chain different procedures together. If that's not the issue then it would help to explain exactly how it "doesn't work" - do you see an error or ??? — Tim Williams, Aug 28 '19 at 15:05
@TimWilliams thanks for the suggestion! Now, when I run the sub (by itself) on another sheet it works however when I run it through the button it still doesn't work. — court_k, Aug 28 '19 at 15:13
@TimWilliams sorry, I should have been clearer! When I meant not work, I meant that it does not split the text into separate columns. It also does not give any errors. — court_k, Aug 28 '19 at 15:31

Ron Rosenfeld · Answer 1 · 2019-08-28T15:40:58.033

3

Although there may be a problem with Active Sheet implicit references (and those should be corrected), most likely the problem is that the Query is not finishing before formatUSDAWeekly is being called.

There are a variety of possible solutions to this problem including:

disable Background Refresh .BackgroundQuery = False
use DoEvents after the query, but this doesn't always work
use .RefreshAll with a blank query after pullFrom610 that is named something like zzzzzz so it runs last (the queries refresh in alphabetical order)

edited Aug 28 '19 at 15:40

answered Aug 28 '19 at 15:39

Ron Rosenfeld

53,870
7
28
60

Ha, totally missed that! – Mathieu Guindon Aug 28 '19 at 15:40
@MathieuGuindon I had a similar problem a few weeks ago, and spent some time troubleshooting. – Ron Rosenfeld Aug 28 '19 at 15:43
@RonRosenfeld ooh! That might be it. I'm not going to lie, this is little beyond the scope of my knowledge. When would I disable the background refresh? – court_k Aug 28 '19 at 15:45
@court_k anywhere before the `.Refresh` member call in the `With ws.QueryTables.Add(...)` block – Mathieu Guindon Aug 28 '19 at 15:46
++ Good Catch :) – Siddharth Rout Aug 28 '19 at 15:58

Louis · Accepted Answer · 2019-08-28T15:46:05.020

1

The reason is probably due to the change of activeSheet made by the call to pullFrom610() when you click the button.

To solve this, make an explicit call to your Destination Range in Sub formatUSDAWeekly.

Edit:

As suggested by @RonRosenfeld, there was a second problem with the query not finishing it's job in time. The solution to this is to put .BackgroundQuery = False. The final code will look like this:

Sub pullFrom610()
'this code was taken from Seamus Abshere
'on SO:https://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("USDA Weekly")

ws.Columns("A:F").ClearContents 'clears the previous information

With ws.QueryTables.Add(Connection:="URL;https://www.ams.usda.gov/mnreports/lm_pk610.txt", Destination:=ws.Range("A1"))

    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .Refresh
    .SaveData = True

End With

End Sub

Sub formatUSDAWeekly()
'this sub pulls information from strictly within the workbook

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook 'sets wb to the workbook that contains the code (i.e. this workbook)

Set ws = wb.Sheets("USDA Weekly")

ws.Range("A:A").TextToColumns Destination:=ws.Range("A:A"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(39, 1), Array(50, 1), Array(52, 1), Array(61, 1), _
        Array(73, 1)), TrailingMinusNumbers:=True

End Sub

Hope this helps.

edited Aug 28 '19 at 15:46

answered Aug 28 '19 at 15:10

Louis

3,592
2
10
18

thanks for the suggestion! I tried this and while this lets me run the sub (by itself) on another sheet, it doesn't work through the button. – court_k Aug 28 '19 at 15:17
When you debugged the function step by step, did you discovered in which function the code fail to run? – Louis Aug 28 '19 at 15:20
the code failed to run in the sub where the code is split, formatUSDAWeekly – court_k Aug 28 '19 at 15:21
when I ran the start sub step by step, it worked! Yet, when I click the button formatUSDAWeekly did not work, i.e. did not split the columns – court_k Aug 28 '19 at 15:25
@MathieuGuindon thanks for the links! I have changed my code to include a with statement (I added it the question above), however it still does not work... I'll keep trying to find the implicit reference though! – court_k Aug 28 '19 at 15:39
@MathieuGuindon it worked!!! :) Thanks for all the help! I'll definitely take a look at those add-ins! – court_k Aug 28 '19 at 15:48

Splitting columns doesn't work with button

2 Answers2

Edit: