Link to home
Create AccountLog in
Avatar of Senniger1
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.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!
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image


try this instead of  
'Set MyAccess = CreateObject("Access.Application")
'    MyAccess.Visible = True
'        MyAccess.OpenCurrentDatabase ("W:\DBFILES\Reports\Reports.mdb")


Dim dbPath As String

dbPath = """" & SysCmd(acSysCmdAccessDir) & "\MSAccess.exe""  ""W:\DBFILES\Reports\Reports.mdb"""
Shell dbPath, vbMaximizedFocus



 
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
Avatar of Senniger1
Senniger1

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?

try this


   dbPath = Chr(34) & SysCmd(acSysCmdAccessDir) & "MSAccess.exe" & Chr(34) & " " & Chr(34) & "W:\DBFILES\Reports\Reports.mdb" & Chr(34)

Shell dbPath, vbMaximizedFocus
Same error.  Sub or Function not defined on "SysCmd".
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\CPIReports.mdb" & Chr(34)
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
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!
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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.
sorry, just realized your codes are in excel, and i was insisting the codes that are used in access