PDA

View Full Version : [SOLVED] Force opening a file as a .XML



mattreingold
05-25-2018, 05:26 AM
So I am opening a file from a Materials Testing Machine with the extension .mtwData. It is basically a giant spreadsheet that I need to manipulate and pull data from. When I manually open it as an xml, it is formatted in a way that I can manipulate - Excel prompts me to open as an xml or text only file. The problem is, when I map a directory to open files from (I can see the .mtwData files by specifying to search for them) is that when the vba macro opens the file, it seems to automatically open it as a text only file. I dont get the same prompt when it is opened through the macro to have the option to open as a .xml file. Ideally, I would force open it as an xml and skip all alerts by setting Application.Alerts = false before opening, and then back to true after.

TL/DR: The question is: is there a way to FORCE a file to open in the .xml format?

Thanks!



'//
' Showing Excel Dialog
xlFile = Application.GetOpenFilename("All Excel Files (*.mtwData*)," & _
"*.xls*", 1, "Select Excel File", "Open", False)


' Open selected file
Workbooks.OpenXML xlFile
xlFileName = Right(xlFile, 34)
xlFileNameActual = Left(xlFileName, 8)

' Establish important values from sheet
TotalRows = Rows(Rows.Count).End(xlUp).Row
loadArr = Range("B2:B" & TotalRows).Value
...
'//

mattreingold
05-29-2018, 04:45 AM
Bump

mattreingold
05-29-2018, 05:47 AM
I couldn't actually find out how to do this - I tried opening the spreadsheet and saving as a .xml, but opening it in the first place was by default opening it as a read only, thus saving it then as a .xml was in the read only format, not the desired xml format. I was, however, able to write code to modify the read only file to make it do what I wanted, so I no longer require help. If anyone else in the future needs this solved, I'll leave it here however I'm going to mark it as solved. Thanks!