have excel prompt for a file name and save location

[Guest]

I have a macro that parses a table, filters it according to some criteria,
and then copies the filtered records to a new file.

What code can I use to prompt the user to

1) Enter a file name
2) Browse to a specific network location to save the file (which can vary
each time the macro is run)

I'm assuming these two questions would make use of the MsgBox command but
I'm not sure exactly how it would look. Thanks for any hints.

Dave

 

[Earl Kiosterud]

Dave

Dim FileName as String
FileName = Application.GetOpenFileName

The path will be included in the returned value.

 

[Guest]

Thanks for this response but I'm not sure I understand. Let me be more
specific. Here's what I see when I use the macro recorder to record the
action of (1) navigating to a folder and (2) saving the file:

Sub SaveAs1()
'
' SaveAs1 Macro
' Macro recorded 3/20/2007 by David Nevin Friedman
'

'
ChDir "G:\NADC Finance\D Friedman"
ActiveWorkbook.SaveAs Filename:= _
"G:\NADC Finance\D Friedman\ELR Parsed--test.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub


What I'm trying to figure out is how to make this dynamic; i.e., someone
runs the macro, a dialog box pops up asking the user to navigate to the
desired filepath, and then enter the desired file name.

I'm pretty sure this is possible but I'm not clear on how to do it.

Thanks,

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Dave

Dim FileName as String
FileName = Application.GetOpenFileName

The path will be included in the returned value.

 

[Fred Smith]

Why don't you try Earl's recommendation before you post a reply? You'll find it
will do exactly what you want.

--
Regards,
Fred

Thanks for this response but I'm not sure I understand. Let me be more
specific. Here's what I see when I use the macro recorder to record the
action of (1) navigating to a folder and (2) saving the file:

Sub SaveAs1()
'
' SaveAs1 Macro
' Macro recorded 3/20/2007 by David Nevin Friedman
'

'
ChDir "G:\NADC Finance\D Friedman"
ActiveWorkbook.SaveAs Filename:= _
"G:\NADC Finance\D Friedman\ELR Parsed--test.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub


What I'm trying to figure out is how to make this dynamic; i.e., someone
runs the macro, a dialog box pops up asking the user to navigate to the
desired filepath, and then enter the desired file name.

I'm pretty sure this is possible but I'm not clear on how to do it.

Thanks,

Dave

 

[Guest]

Well I did try the response it prompts me to open a file. I'm not interested
in opening a file. I want to save a file.

Dave

 

[Dave Peterson]

Maybe Earl meant:

Dim myFileName as Variant
myfilename = application.GetSaveAsFilename
if myfilename = false then
exit sub 'user hit cancel
end if

Activeworkbook.saveas filename:=myfilename, fileformat:=xlworkbooknormal

 

[Guest]

Works great, thanks.

Dave