Excel error message is stopping my macro!

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:30 PM GMT

Question

QUESTION: I am writing anMacro for the first time, so i started with the record button. However now I have had to add a user ability to select a .XML file in excel, nowgives an error message that it wasn't giving before that the macro does not know how to deal with. How do I get the script to ignore or say "OK" to this error message and continue on? Code below...

Filename = Application.GetOpenFilename("XML files (*.xml),*.xml", , "Please select ONE XML file to import", , False)

   Workbooks.OpenXML Filename:=Filename, LoadOption:=xlXmlLoadImportToList
   ActiveWindow.LargeScroll ToRight:=8
   ActiveCell.Offset(1, 56).Range("A1:A10").Select
   Selection.Copy
   Windows("CLND_Daily_Standard_IPQA_2010.xls").Activate
   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
       False, Transpose:=True

The macro is fairly simple, it opens a saved xml file, copies a few lines and pastes them into a master sheet. Any help you can offer would be fantastic.

ANSWER: Elliot

without knowing which line causes the error message or what the error message is about the best I can say is use some commands that normally will cause a message to be ignored:

On Error Resume Next Application.Displayalerts = False

Filename = Application.GetOpenFilename("XML files (*.xml),*.xml", , "Please select ONE XML file to import", , False)

  Workbooks.OpenXML Filename:=Filename, LoadOption:=xlXmlLoadImportToList
  ActiveWindow.LargeScroll ToRight:=8
  ActiveCell.Offset(1, 56).Range("A1:A10").Select
  Selection.Copy
  Windows("CLND_Daily_Standard_IPQA_2010.xls").Activate
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=True


If the error is significant to what you are doing, just ignoring it may not get you the results you expect.



---------- FOLLOW-UP ----------

QUESTION: The error occurs at this point:

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

It is an error message telling me that some of the data has been imported as text, which is fine, but it stops the script.

Answer

Elliot

That sounds like a warning rather than an error.

If that is the case then that should be handled by the

Application.DisplayAlerts = False

command. If it doesn't, then it doesn't sound like it can be suppresed.

If it really is an error, then

On Error resume next

should handle it.  If it doesn't, then again, it doesn't sound like it can be suppressed.  

There really aren't any other choices.

Advertisement

©2021 eLuminary LLC. All rights reserved.