I need VBA assistance to set a directory in Access module

[Guest]

Good afternoon,

I need a VBA command that will set the current directory in Access to a
specific folder, so that even if I change the current directory by opening a
file from a different directory, when the sub runs, the directory will be set
to specific folder when I use the show command for the open dialogue box.

This is my code, but for some reason doesn't open the proper directory and
only opens whatever the last directory opened in the application.
Dim OBJ As Object
Set OBJ = GetObject(, "EXCEL.APPLICATION")
Dim SPATh As String
OBJ.Visible = True
SPATh = "C:\SCR DATABASE\CURRENT YEAR Data And Charts\SCR\SCR SUMMARY"
ChDrive SPATh
ChDir SPATh
OBJ.Application.Dialogs(xlDialogOpen).Show

 

[Dirk Goldgar]

Good afternoon,

I need a VBA command that will set the current directory in Access to
a specific folder, so that even if I change the current directory by
opening a file from a different directory, when the sub runs, the
directory will be set to specific folder when I use the show command
for the open dialogue box.

This is my code, but for some reason doesn't open the proper
directory and only opens whatever the last directory opened in the
application. Dim OBJ As Object
Set OBJ = GetObject(, "EXCEL.APPLICATION")
Dim SPATh As String
OBJ.Visible = True
SPATh = "C:\SCR DATABASE\CURRENT YEAR Data And Charts\SCR\SCR
SUMMARY" ChDrive SPATh
ChDir SPATh
OBJ.Application.Dialogs(xlDialogOpen).Show

Have you tried this:

OBJ.Application.Dialogs(xlDialogOpen).Show SPATh

?

 

[Guest]

I 've not tried that. Thanks.

Have you tried this:

OBJ.Application.Dialogs(xlDialogOpen).Show SPATh

?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)