Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

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
0
DarrenJackson
Asked:
DarrenJackson
3 Solutions
 
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
 
MacroShadowCommented:
In this thread you will find two other methods.
0
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now