Solved

create a variable "SAVE AS" in excel 2010 vba

Posted on 2014-04-29
6
791 Views
Last Modified: 2014-04-29
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
Comment
Question by:DarrenJackson
6 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40029289
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
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 150 total points
ID: 40029296
In this thread you will find two other methods.
0
 

Author Comment

by:DarrenJackson
ID: 40029297
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 40029302
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
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 100 total points
ID: 40029306
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
 

Author Closing Comment

by:DarrenJackson
ID: 40029356
Great thanks the suggestions have given me a pointer to complete my code

Many Thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question