Senniger1
asked on
Open Access 2003 Database Mazimized from Excel
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.Appli
MyAccess.Visible = True
MyAccess.OpenCurrentDataba
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!
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.Appli
MyAccess.Visible = True
MyAccess.OpenCurrentDataba
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!
You can add a DoCmd.Maximize in an AutoExce macro in the Access database. Then, when opened by the Excel macro, Access will force the window max.
Can you not use:
MyAccess.DoCmd.Maximize
MyAccess.DoCmd.Maximize
ASKER
capricorn1:
Sorry for the delay. When I try your solution, the "SysCmd" is highlighted and I'm getting a message "Compile error: Sub or Function not defined".
Any thoughts?
Sorry for the delay. When I try your solution, the "SysCmd" is highlighted and I'm getting a message "Compile error: Sub or Function not defined".
Any thoughts?
try this
dbPath = Chr(34) & SysCmd(acSysCmdAccessDir) & "MSAccess.exe" & Chr(34) & " " & Chr(34) & "W:\DBFILES\Reports\Report
Shell dbPath, vbMaximizedFocus
ASKER
Same error. Sub or Function not defined on "SysCmd".
ASKER
Okay I did a little research and then dropped the SysCmd. So I changed it to the following and it worked.
dbPath = Chr(34) & (acSysCmdAccessDir) & "MSAccess.exe" & Chr(34) & " " & Chr(34) & "W:\DBFILES\CPIReports\CPI
Shell dbPath, vbMaximizedFocus
Think this would be okay since it's working?
dbPath = Chr(34) & (acSysCmdAccessDir) & "MSAccess.exe" & Chr(34) & " " & Chr(34) & "W:\DBFILES\CPIReports\CPI
Shell dbPath, vbMaximizedFocus
Think this would be okay since it's working?
try doing a compact and repair
from VBA window
tools > references
* look if there are references prefixed with "MISSING"
- uncheck and locate available version of the reference and select
do a DEBUG > Compile
correct any errors raised
from VBA window
tools > references
* look if there are references prefixed with "MISSING"
- uncheck and locate available version of the reference and select
do a DEBUG > Compile
correct any errors raised
ASKER
I did all that, but there were no issues. It works when I removed the SysCmd.
Can't I just use your solution without the SysCmd?
Thanks!
Can't I just use your solution without the SysCmd?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works like a charm. Thank you so much for everything.
I'm giving you all the points since I tried the other solutions offered and they didn't work for this particular instance.
I'm giving you all the points since I tried the other solutions offered and they didn't work for this particular instance.
sorry, just realized your codes are in excel, and i was insisting the codes that are used in access
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