Solved

create a variable "SAVE AS" in excel 2010 vba

Posted on 2014-04-29
6
783 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

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 68

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now