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

qlikuser222 — Thu, 26 May 2016 17:47:21 GMT

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

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

tamilarasu — Thu, 26 May 2016 23:00:41 GMT

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"