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
...
'//
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
...
'//