VBA code fails as soon as XML document is loaded

Question

I have the below code which takes an XML file from a shared location and loads it into Excel. As soon as the file is opened, i get a "Run-time error '1004': Application-defined or object defined error" message.

Sub Load_XML()

Dim xml_file_path As String
Dim file_date As String

Worksheets("Start").Activate
file_date = Range("B1").Value

xml_file_path = "Y:\mydrive\" & file_date & "-000000_RREP1002.XML"

Workbooks.OpenXML Filename:= _
        xml_file_path _
        , LoadOption:=xlXmlLoadImportToList

Dim lstrow as Integer
Dim r as Range

lstrow = ActiveSheet.UsedRange.Rows.Count 

Set r = Range("A2:AF & lstrow")

The code errors before the last line is executed, i.e. as soon as the OpenXML is done.

Any ideas please what could be causing this issue?

thanks!

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jun 23 '21 at 11:52
hi, sorry for being thick, but i am not sure how i can avoid the Select function here as I need to select a range of data which I dont know how many rows it is (it changes day on day) so the way i do it is selecting the top row and then using XlDown command. If i declare a range as a variable, would i not have to then somehow determine how many rows are in the dataset? — st87_top, Jun 23 '21 at 13:46
For that have a look here: [Better way to find last used row](https://stackoverflow.com/a/38882823) — Pᴇʜ, Jun 23 '21 at 13:49
thanks, i have updated my code to amend the last line to the following, but i still get the same error: lstrow = ActiveSheet.UsedRange.Rows.Count Set r = Range("A2:AF & lstrow") — st87_top, Jun 23 '21 at 14:47
What format is the filename `YYYY-MM-DD_000000_RREP1002.XML` ? — CDP1802, Jun 23 '21 at 14:55
The code is still the same in the question. Can you please [edit] and update the question with the new information you got? — Pᴇʜ, Jun 23 '21 at 14:57
yep, without the hyphens, so todays file is 20210623_000000_RREP1002.XML — st87_top, Jun 23 '21 at 14:58
Have you checked tha value of `Range("B1").Value`? Probably you need to format it correctly if it as a numeric date and not a text (looking like a date). Try `file_date = Format$(Range("B1").Value, "YYYYMMDD")`. Also check one time you have a hypen one time an underscore. — Pᴇʜ, Jun 23 '21 at 15:00
Also note that Excel has more rows than `Integer` can take. So you need to declare row counting variables as `Long` like: `Dim lstrow As Long`. Finally `Range("A2:AF & lstrow")` needs to be `Range("A2:AF" & lstrow)` the variable needs to be outside the string. — Pᴇʜ, Jun 23 '21 at 15:02
that part of the code works fine though as it can locate the file and open it. It is only after opening the file that my code errors out? — st87_top, Jun 23 '21 at 15:04
@Pᴇʜ thank you very much! that last part has fixed it! Apologies for my basic questions. — st87_top, Jun 23 '21 at 15:08
@st87_top I added it as an anwser with some improvements, so you can mark it as solved. — Pᴇʜ, Jun 23 '21 at 15:40

score 0 · Answer 1 · answered Jun 23 '21 at 15:39

I would refactor the code like below.

Public Sub Load_XML()
    Dim wsStart As Worksheet
    Set wsStart = ThisWorkbook.Worksheets("Start")
 
    Dim file_date As String
    file_date = wsStart.Range("B1").Value  ' make sure all ranges have a worksheet specified!
    
    Dim xml_file_path As String
    xml_file_path = "Y:\mydrive\" & file_date & "-000000_RREP1002.XML"
    
    Workbooks.OpenXML Filename:=xml_file_path, LoadOption:=xlXmlLoadImportToList
    
    Dim lstRow As Long
    
    
    lstRow = wsStart.UsedRange.Rows.Count 
    ' I recommend a more reliable method to find the 
    
    Dim r As Range
    Set r = wsStart.Range("A2:AF" & lstRow)

I recommend a more reliable method to find the last usted row than using

lstRow = wsStart.UsedRange.Rows.Count

It is better you use

lstRow = wsStart.Cells(wsStart.Rows.Count, "A").End(xlUp).Row

This will find the last used row in column A. Adjust the column to one that has always data and no blanks.

VBA code fails as soon as XML document is loaded

1 Answers1