Save Export file location

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

Is it possible to have a pop-up that lets the user select
where they would like to have the file saved? Also what
if you wanted it to save to the current machines desktop
as default?
Thanks.
DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True
 
John,
Can you help me out with adding the FileDialog in to my
code. I looked it up in the HElp and tried to use the
exmaple but keep getting and error 'user defined type
not defined' error.
Thanks Raj

DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True
-----Original Message-----
Hi Raj,

You can do this by using the code at
http://www.mvps.org/access/api/api0001.htm to display the standard
File|Save dialog.

ALternatively if you're using a recent version of Access you can use the
Application FileDialog object

Is it possible to have a pop-up that lets the user select
where they would like to have the file saved? Also what
if you wanted it to save to the current machines desktop
as default?
Thanks.
DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop",
Click to expand...
True

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Raj,

I'm not going to guess. Please post the code you were using and indicate
the line at which the error occurs. Also, which versions of Access and
Windows are you using?

John,
Can you help me out with adding the FileDialog in to my
code. I looked it up in the HElp and tried to use the
exmaple but keep getting and error 'user defined type
not defined' error.
Thanks Raj

DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True
-----Original Message-----
Hi Raj,

You can do this by using the code at
http://www.mvps.org/access/api/api0001.htm to display the standard
File|Save dialog.

ALternatively if you're using a recent version of Access you can use the
Application FileDialog object

Is it possible to have a pop-up that lets the user select
where they would like to have the file saved? Also what
if you wanted it to save to the current machines desktop
as default?
Thanks.
DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop",
Click to expand...
True

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
John,
Here is the code.
Thanks

On Error GoTo Err_cmdExport_Click

Dim stDocName As String
Dim dlgSaveAs As FileDialog

Set dlgSaveAs = Application.FileDialog( _
FileDialogType:=msoFileDialogSaveAs)

dlgSaveAs.Show

DoCmd.TransferSpreadsheet acExport, 8, _
"MasterData", "dlgsaveAs", True

Exit_cmdExport_Click:
Exit Sub

Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click

End Sub
-----Original Message-----
Raj,

I'm not going to guess. Please post the code you were using and indicate
the line at which the error occurs. Also, which versions of Access and
Windows are you using?

John,
Can you help me out with adding the FileDialog in to my
code. I looked it up in the HElp and tried to use the
exmaple but keep getting and error 'user defined type
not defined' error.
Thanks Raj

DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True
-----Original Message-----
Hi Raj,

You can do this by using the code at
http://www.mvps.org/access/api/api0001.htm to display the standard
File|Save dialog.

ALternatively if you're using a recent version of
Click to expand...
Access
you can use the
Application FileDialog object

On Thu, 26 Aug 2004 19:02:49 -0700, "Raj"

Is it possible to have a pop-up that lets the user select
where they would like to have the file saved? Also what
if you wanted it to save to the current machines desktop
as default?
Thanks.
DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Raj,

As I understand it you want to let the user select a folder in which to
save the exported file, rather than specify a file name. For that you
need to use Application.FileDialog(msoFiledialogFolderPicker). In any
case, according to
http://support.microsoft.com/default.aspx?scid=kb;EN-US;282335 you
cannot use msoFileDialogSaveAs in Access 2002 (I don't know whether the
problem has been fixed in Access 2003). The other problem in your code
is that you didn't do anything to pass the selected name from the dialog
to TransferSpreadsheet. What you need is something like the code below.

Private Sub cmdExport_Click()

Const FILENAME = "XXX.xls"
Const INITIAL_FOLDER = "C:\Folder\Subfolder"
Dim strFileName As String
Dim dlgD As Office.FileDialog

On Error GoTo Err_cmdExport_Click

Set dlgD = Application.FileDialog(msoFileDialogFolderPicker)
With dlgD
.Title = "Select location to save file"
.InitialFileName = INITIAL_FOLDER
.Show
If .SelectedItems.Count = 0 Then
MsgBox "User clicked Cancel", vbInformation + vbOKOnly
GoTo Exit_cmdExport_Click:
End If
strFileName = .SelectedItems(1) & "\" & FILENAME
End With
MsgBox "About to save to " & strFileName, _
vbInformation + vbOKOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MainData", strFileName, True

Exit_cmdExport_Click:
Set dlgD = Nothing
Exit Sub


Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click
End Sub



John,
Here is the code.
Thanks

On Error GoTo Err_cmdExport_Click

Dim stDocName As String
Dim dlgSaveAs As FileDialog

Set dlgSaveAs = Application.FileDialog( _
FileDialogType:=msoFileDialogSaveAs)

dlgSaveAs.Show

DoCmd.TransferSpreadsheet acExport, 8, _
"MasterData", "dlgsaveAs", True

Exit_cmdExport_Click:
Exit Sub

Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click

End Sub
-----Original Message-----
Raj,

I'm not going to guess. Please post the code you were using and indicate
the line at which the error occurs. Also, which versions of Access and
Windows are you using?

John,
Can you help me out with adding the FileDialog in to my
code. I looked it up in the HElp and tried to use the
exmaple but keep getting and error 'user defined type
not defined' error.
Thanks Raj

DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True

-----Original Message-----
Hi Raj,

You can do this by using the code at
http://www.mvps.org/access/api/api0001.htm to display
the standard
File|Save dialog.

ALternatively if you're using a recent version of Access
you can use the
Application FileDialog object

On Thu, 26 Aug 2004 19:02:49 -0700, "Raj"

Is it possible to have a pop-up that lets the user
select
where they would like to have the file saved? Also what
if you wanted it to save to the current machines
desktop
as default?
Thanks.
DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop",
True

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
John,
Thank you!!! I am just learning Coding and would have
never figured this out...
Thanks again
Raj
-----Original Message-----
Raj,

As I understand it you want to let the user select a folder in which to
save the exported file, rather than specify a file name. For that you
need to use Application.FileDialog
Click to expand...
(msoFiledialogFolderPicker). In any
case, according to
http://support.microsoft.com/default.aspx?scid=kb;EN- US;282335 you
cannot use msoFileDialogSaveAs in Access 2002 (I don't know whether the
problem has been fixed in Access 2003). The other problem in your code
is that you didn't do anything to pass the selected name from the dialog
to TransferSpreadsheet. What you need is something like the code below.

Private Sub cmdExport_Click()

Const FILENAME = "XXX.xls"
Const INITIAL_FOLDER = "C:\Folder\Subfolder"
Dim strFileName As String
Dim dlgD As Office.FileDialog

On Error GoTo Err_cmdExport_Click

Set dlgD = Application.FileDialog (msoFileDialogFolderPicker)
With dlgD
.Title = "Select location to save file"
.InitialFileName = INITIAL_FOLDER
.Show
If .SelectedItems.Count = 0 Then
MsgBox "User clicked Cancel", vbInformation + vbOKOnly
GoTo Exit_cmdExport_Click:
End If
strFileName = .SelectedItems(1) & "\" & FILENAME
End With
MsgBox "About to save to " & strFileName, _
vbInformation + vbOKOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MainData", strFileName, True

Exit_cmdExport_Click:
Set dlgD = Nothing
Exit Sub


Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click
End Sub



John,
Here is the code.
Thanks

On Error GoTo Err_cmdExport_Click

Dim stDocName As String
Dim dlgSaveAs As FileDialog

Set dlgSaveAs = Application.FileDialog( _
FileDialogType:=msoFileDialogSaveAs)

dlgSaveAs.Show

DoCmd.TransferSpreadsheet acExport, 8, _
"MasterData", "dlgsaveAs", True

Exit_cmdExport_Click:
Exit Sub

Err_cmdExport_Click:
MsgBox Err.Description
Resume Exit_cmdExport_Click

End Sub
-----Original Message-----
Raj,

I'm not going to guess. Please post the code you were using and indicate
the line at which the error occurs. Also, which
Click to expand...
versions
of Access and
Windows are you using?

On Fri, 27 Aug 2004 08:34:43 -0700, "Raj"

John,
Can you help me out with adding the FileDialog in to my
code. I looked it up in the HElp and tried to use the
exmaple but keep getting and error 'user defined type
not defined' error.
Thanks Raj

DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1\Desktop", True

-----Original Message-----
Hi Raj,

You can do this by using the code at
http://www.mvps.org/access/api/api0001.htm to display
the standard
File|Save dialog.

ALternatively if you're using a recent version of Access
you can use the
Application FileDialog object

On Thu, 26 Aug 2004 19:02:49 -0700, "Raj"

Is it possible to have a pop-up that lets the user
select
where they would like to have the file saved? Also what
if you wanted it to save to the current machines
desktop
as default?
Thanks.
DoCmd.TransferSpreadsheet acExport, 8, _
"MainData", "C:\Documents and Settings\DB1 \Desktop",
True

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top