Macros to ask usres for path to save excel on their machine.

[qlikuser222]

Hi,

Can anyone please help in solving the below issue.

In the below macro where can i put the message also also path which asks users to save excel object at thedesired location on their machine.

Sub Export 

     'Create the Excel spreadsheet  

     Set excelFile = CreateObject("Excel.Application") 

     excelFile.Visible = true 

     'Create the WorkBook 

     Set curWorkBook = excelFile.WorkBooks.Add 

     'Create the Sheet 

     Set curSheet = curWorkBook.WorkSheets(1) 

     'Get the chart we want to export 

     Set tableToExport = ActiveDocument.GetSheetObject("XXX") 

     Set chartProperties = tableToExport.GetProperties 

     tableToExport.CopyTableToClipboard true 

     'Get the caption 

     chartCaption = tableToExport.GetCaption.Name.v 

     'MsgBox chartCaption 

     'Set the first cell with the caption 

     curSheet.Range("A1") = chartCaption 

     'Paste the rest of the chart 

     curSheet.Paste curSheet.Range("A2") 

     excelFile.Visible = true 

     'Save the file and quit excel 

     'curWorkBook.SaveAs filePath 

     'curWorkBook.Close 

     'excelFile.Quit 

     'Cleanup 

     Set curWorkBook = nothing 

     Set excelFile = nothing 

End Sub 

[tamilarasu · Accepted solution]

Hi,

I have modified the code as per your request. If this is not working in server (or access point), I'll show you another method.

Sub Export 
Dim objShell
Dim objFolder
Dim excelFile
Dim curWorkBook 

  Set objShell = CreateObject("Shell.Application")
   Set objFolder = objShell.BrowseForFolder(0, "Select the path to save the excel:", 1, "Computer")
   On Error Resume Next
      If objFolder Is Nothing Then
        Set objShell = nothing 
        Set objFolder = nothing
        Exit sub
      End If
 'File name
 FileName = "Test_" & ActiveDocument.Evaluate("date(Now(), 'DD-MM-YYYY hhmmss')") & ".xlsx"

     'Create the Excel spreadsheet  
     Set excelFile = CreateObject("Excel.Application") 
     excelFile.Visible = False
     'Create the WorkBook 
     Set curWorkBook = excelFile.WorkBooks.Add 
     'Create the Sheet 
     Set curSheet = curWorkBook.WorkSheets(1) 

     'Get the chart we want to export 
     Set tableToExport = ActiveDocument.GetSheetObject("CH02") 
     Set chartProperties = tableToExport.GetProperties 
     tableToExport.CopyTableToClipboard true 

     'Get the caption 
     chartCaption = tableToExport.GetCaption.Name.v 
     'MsgBox chartCaption 

     'Set the first cell with the caption 
     curSheet.Range("A1") = chartCaption 
     'Paste the rest of the chart 
     curSheet.Paste curSheet.Range("A2") 

     'Save the file and quit excel 
     curWorkBook.SaveAs objFolder.self.path & "\" &  FileName
     curWorkBook.Close 
     excelFile.Quit 

    Msgbox "File exported successfully!!"
     'Cleanup 
     Set objShell = nothing 
     Set objFolder = nothing
     Set curWorkBook = nothing 
     Set excelFile = nothing 
End Sub