StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel 2003 VBA error 1004 after XML data import
text
Body
copied!<p>I built an macro which takes different XML input files and imports each one into a different worksheet in a workbook. It then saves the workbook. This works a treat 98% of the time with hundreds of users, but sometimes fails when saving the workbook with "Error 1004: Microsoft Office Excel cannot access the file 'C:\SymmPiT'.</p> <p>I found it relates to when one of the XML files is too large for Excel to import in section "ImportDisks:". Trying to import the SYMDISK.XML file manually into a blank workbook (using "Data - Import - XML") brings Excel error "XML Import Error: Some Data failed to import". </p> <p>I am trying to have Excel VBA ignore this XML import error. This works in the macro at that stage but then fails when saving the workbook further down in the code (marked in red).</p> <p>How can I avoid Excel "barfing" when saving the file, even if there is an import error? Or can the XML file be checked somehow before importing so I can avoid trying the import?</p> <p>Code exceprts are below, many thanks for responses! :-)</p> <pre><code>Sub ImportData() On Error GoTo SPErrorHandler Application.DisplayAlerts = False Sheets("SymmPiT").Select Range("F15").Select Dim SaveName As String Dim Section As Integer ImportDisks: Section = 7 On Error GoTo ImportLocks Sheets.Add.Name = "Disks" Application.ScreenUpdating = False Range("A2").Select ActiveCell.FormulaR1C1 = "from symdisk list -v" If (Dir("C:\SymmPiT\SYMDISK.XML") > "") Then ActiveWorkbook.XmlImport URL:="C:\SymmPiT\SYMDISK.XML", ImportMap:=Nothing, overwrite:=True, Destination:=Range("$A$4") ActiveWorkbook.XmlMaps("SymCLI_ML_Map").Delete Set Contents = Worksheets("Disks").Range("A5") If Contents.Text = "" Then MsgBox "Information" & vbNewLine & vbNewLine & "Disks data file has not imported, too many rows of data for Excel 2003" End If Else Range("A4").Select ActiveCell.FormulaR1C1 = "No Disk Data file SYMDISK.XML found" End If Range("A2").Select Application.ScreenUpdating = True ImportLocks: ‘ Code continues here, removed from this post…. Application.ScreenUpdating = True SaveAsExcelFile: ' SaveAsExcelFile Section = 43 On Error GoTo SPErrorHandler SaveName = Worksheets("SymmPiT").Range("F19").Text ' Clear any outstanding error Err.Clear ' Save output file ActiveWorkbook.SaveAs Filename:="C:\SymmPiT\" & SaveName & ".xls" Range("F15").Select </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload