create a variable "SAVE AS" in excel 2010 vba

Guys

I am after some help. I have this piece of code that works fine but for it to work I have to hard code the location and name in to the vba for it to save a copy what I would like is for it to be a little slicker and ask the user to choose location and name as in the FILE SAVE AS window

Here is the code

Sub Export()
Dim ws As Worksheet
Dim flPathName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
flPathName = "\\htsvr1\mbro\Accounts\TEST\" & ActiveSheet.Range("B1")

    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
   

Set ws = ActiveSheet
ActiveSheet.Range("A3:XFD20000").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs flPathName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close savechanges:=False

Sheets("Sheet1").Select
    Range("B1").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


Any Ideas

Darren
DarrenJacksonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

 flPathName = Application.GetSaveAsFilename()
If flPathName <> "" Then
    ActiveWorkbook.SaveAs flPathName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Else
    MsgBox "No File Saved"
    Exit Sub
End If

Open in new window

0
 
MacroShadowCommented:
There is more than one way to skin a cat, this is the shortest easiest method:
flPathName = Application.GetOpenFilename("Excel workbooks,*.xls*")

Open in new window

0
 
MacroShadowConnect With a Mentor Commented:
In this thread you will find two other methods.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DarrenJacksonAuthor Commented:
Thanks MacroShadow but this is asking for an existing file what if the file doesn't exist hence the "SAVE AS" option

Thanks for replying as quick as you have
0
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
The InputBox call is one way to ask for input:
flPathName = InputBox("Choose path") & ActiveSheet.Range("B1")

Open in new window

or display a file dialog for the folder selection:
Dim fd as FileDialog
set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.InitialFileName = "\\htsvr1\mbro\Accounts\TEST\" & ActiveSheet.Range("B1")
If fd.Show = - 1 Then
  flPathName = fd.SelectedItems(1)& ActiveSheet.Range("B1") 
  ActiveWorkbook.SaveAs lPathName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End If

Open in new window

or similar.
0
 
DarrenJacksonAuthor Commented:
Great thanks the suggestions have given me a pointer to complete my code

Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.