Solved

create a variable "SAVE AS" in excel 2010 vba

Posted on 2014-04-29
6
786 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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