CODEWITHSUNDEEP
vbaexcelvbscript
nwpulele
nwpulele

Reputation: 219

How to deal with Excel popup when using Workbooks.OpenXML

I'm trying to use vbs to open XML via Excel.

Below is my code:

Set myxl = createobject("excel.application")
myxl.DisplayAlerts = False
myxl.Application.Visible = true
myxl.Workbooks.OpenXML "SampleXML.XML",1,xlXmlLoadImportToList

But when code runs, Excel will pop up an option to ask how to open this file. I'm sorry that I don't have enough reputation to post images. This popup will ask you whether open XML as "As an XML table" (which I want, but I don't want popup), "As a read-only workbook", "Use the XML Source task pane".

How do I aviod this pop up?

Upvotes: 1

Views: 1337

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

The problem is xlXmlLoadImportToList. It is an Excel Constant and not a VBScript Constant. It has a value 2. You can check that in the Immediate window of Excel as shown below.

enter image description here

Try this :)

Const xlXmlLoadImportToList = 2
Set myxl = createobject("excel.application")
myxl.DisplayAlerts = False
myxl.Application.Visible = true
myxl.Workbooks.OpenXML "C:\Test.XML",1,xlXmlLoadImportToList

Upvotes: 2

Related Questions