Avoid "Save as" dialog while converting Excel to XML using VBA script

Question

I am trying to write a VBA Script which converts the excel file to XML. I am fetching the file name using Application.GetSaveAsFilename() function. But this function opens up the "Save As" dialog box. I want to suppress the dialog such that the user is not prompted to manually click save each time the code is run. Instead, the XML should be silently generated at the location hard-coded.

Code:

Sub BasicRTE()
    Dim FileName As Variant
    Dim Sep As String
    Dim Ws As Worksheet
    Dim autoSetFileName As String
    Dim folderName As String
    Dim location As Integer

    ChDrive (Left(ThisWorkbook.Path, 1))
    ChDir ThisWorkbook.Path
    ChDir ".."
    ChDir "InputFiles"
    Application.SendKeys ("{ENTER}")
    FileName = Application.GetSaveAsFilename( _
      InitialFileName:=ThisWorkbook.Worksheets(1).Name, _
      FileFilter:="Xml Files (*.xml),*.xml")
    location = InStrRev(FileName, "\", , vbTextCompare)
    folderName = Mid(FileName, 1, location - 1)        
    For Each Ws In ThisWorkbook.Worksheets
        If InStr(1, Ws.Name, "#", vbTextCompare) <> 1 Then
            ExportToMyXMLFile FName:=CStr(folderName & "\" & Ws.Name & ".xml"), Sep:=CStr(Sep), _
            AppendData:=False, Ws:=Ws
        End If
    Next
End Sub

@Mark Sorry didn't include the code for ExportToMyXml as that part of code was running perfectly without any issue so thought it would be irrelevant. — kamal pamnani
– kamal pamnani, Commented Feb 22, 2017 at 4:31

cxw · Accepted Answer · 2017-02-21 14:12:40Z

It appears that the only thing you are using Application.GetSaveAsFilename for is to get the path to the InputFiles path with respect to the location of ThisWorkbook into folderName. The operating system already provides that! The following changes should work (but I have not tested them myself):

' fileName = ...                 ' don't need this
' location = ...                 ' or this
folderName = ThisWorkbook.Path & "\..\InputFiles"   ' e.g., C:\Users\Foo\Documents\..\InputFiles

Alternatively, if you want a cleaner string,

Dim location as Long     ' Never use Integer unless you are calling Win32 or something else esoteric

' Don't need any of this unless later code relies on the current directory
' (which it shouldn't, for robustness).
'ChDrive (Left(ThisWorkbook.Path, 1))
'ChDir ThisWorkbook.Path
'ChDir ".."
'ChDir "InputFiles"
'Application.SendKeys ("{ENTER}")
'FileName = Application.GetSaveAsFilename( _
'  InitialFileName:=ThisWorkbook.Worksheets(1).Name, _
'  FileFilter:="Xml Files (*.xml),*.xml")
folderName = ThisWorkbook.Path
location = InStrRev(folderName, "\", , vbTextCompare)
folderName = Mid(folderName, 1, location) & "InputFiles"
For Each ws ...

The InStrRev+Mid drops the last path component, just like .., and then the & "InputFiles" puts InputFiles on the end.

One caution: ThisWorkbook.Path is an empty string for a new, unsaved workbook. Make sure your workbook is saved to disk before using the above.

Edit another caution: you are using ws.Name directly in making filenames. However, sheet names can include text that filenames cannot. I can name a sheet CON or <foo>, but neither of those is valid in a filename. Here's one example of sanitizing filenames (a quick Google result — not tested). However, even that example does not appear to check for reserved names.

_{Reserved names: CON, PRN, AUX, NUL, COM1, COM2, COM3, COM4, COM5, COM6, COM7, COM8, COM9, LPT1, LPT2, LPT3, LPT4, LPT5, LPT6, LPT7, LPT8, and LPT9 per MS).}

The workbook will be presaved as this will be used in my automation framework so using ThisWorkbook.path is not an issue. Also the sheet names are predefined so less chance it will be a reserved name. The code worked like a charm. Thanks !!

Collectives™ on Stack Overflow

Avoid "Save as" dialog while converting Excel to XML using VBA script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related