I have a macro in Excel 2003 which saves an Excel spreadsheet to a desktop and then opens an Access 2003 database. Here is my code...
Sub Save()
' Save Queries as Excel Spreadsheets
' Keyboard Shortcut: Ctrl+m
Application.WindowState = xlMaximized
Dim strFile As String
Dim strFindID As String
Dim stLinkCriteria As String
Dim Answer As String
Dim MyNote As String
'Place your text here
MyNote = "Save File Automatically?"
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")
'Code for Yes button Press
If Answer = vbNo Then
' MsgBox "You indicated this is RBG's Report. Save File as RBGP or RBGT!"
strFindID = InputBox("Enter Names")
strFile = Environ("USERPROFILE") & "\Desktop"
stLinkCriteria = strFile & "\" & strFindID
Else
'Code for No button Press
strFindID = Environ("UserName")
strFile = Environ("USERPROFILE") & "\Desktop"
stLinkCriteria = strFile & "\" & strFindID
End If
ActiveWorkbook.SaveAs Filename:= _
stLinkCriteria, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close False
Application.Quit
' Open Access Database
Set MyAccess = CreateObject("Access.Application")
MyAccess.Visible = True
MyAccess.OpenCurrentDatabase ("W:\DBFILES\Reports\Reports.mdb")
End Sub
The only thing I can't do is force the Access database to open MAXIMIZED. I know all about the "DoCmd.Maximize" which I can put in the Access form and this all works fine. However, when I open the database from Excel it doesn't work so I feel I need to put something here to tell it to open maximizied.
Can anyone help me!
Thanks!
try this instead of
'Set MyAccess = CreateObject("Access.Appli
' MyAccess.Visible = True
' MyAccess.OpenCurrentDataba
Dim dbPath As String
dbPath = """" & SysCmd(acSysCmdAccessDir) & "\MSAccess.exe"" ""W:\DBFILES\Reports\Repor
Shell dbPath, vbMaximizedFocus