Exporting Macro

**christoph13** · 02-09-2017, 06:38 AM

Hi, I currently have the below Macro exporting a sheet from Excel. However whereas at present the macro exports the sheet with a specific name and saves to the H Drive. I would like the macro to export with the same naming arrangement, but ask where to save.

Please help.

Sub Button1_Click()
    Dim oApp As Object
    Dim oMail As Object
    Dim WB As Workbook
    Dim FileName As String
    Dim wSht As Worksheet
    Dim shtName As String

    Application.ScreenUpdating = False

    ' Make a copy of the active worksheet
    ' and save it to a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook

    FileName = WB.Worksheets(1).Name & " RFI TRACKER " & Replace(Date, "/", "-")
    On Error Resume Next
    Kill "H:\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:="H:\" & FileName
End Sub

**kev_** · 02-09-2017, 07:59 AM

Try this
- allows you to specify a default path, which is then placed in an input box for user to amend

replace:

WB.SaveAs FileName:="H:\" & FileName

with:

 Dim myPath As String
    myPath = "D:\Documents\myFolder"  'whatever the default path should be
    myPath = Application.InputBox("Enter Path", "File Path", myPath)
    WB.SaveAs Filename:=myPath & "\" & Filename

**christoph13** · 02-20-2017, 12:09 PM

Hi,

Unfortunately that doesn't really solve my problem. I would like the Macro Button to export the sheet, but bring up teh Save As box, so we can visually choose which folder to save it in. See attached.

Is this possible?

Capture 1.PNG

Regards,

Chris

**TMS** · 02-20-2017, 12:30 PM

Correction:

Dim fName
fName = ""
fName = Application.GetSaveAsFilename
If fName <> "" And fName <> False Then
    MsgBox fName
Else
    MsgBox "nothing selected"
End If

**TMS** · 02-20-2017, 12:23 PM

Dim fName As String
fName = Application.GetSaveAsFilename
If fName <> "" Then
    MsgBox fName
Else
    MsgBox "nothing selected"
End If

**christoph13** · 02-20-2017, 12:34 PM

Hi,

Thats much better. However it isnt bringing through the file name as below:

Capture 2.PNG

Exporting Macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Exporting Macro

Re: Exporting Macro

Re: Exporting Macro

Re: Exporting Macro

Re: Exporting Macro

Re: Exporting Macro

Thread Information

Users Browsing this Thread

Similar Threads

Exporting Workbook without Macro/Formulas

Exporting Client Sheet into CVS via Macro

Exporting Data from SQL Server to Excel using Macro

exporting excel to access macro

Timed macro for printing and exporting

exporting a macro

Exporting the same Excel macro to 60 workbooks

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions