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!
Microsoft ExcelMicrosoft AccessVB Script

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)
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
Avatar of Senniger1
Senniger1

ASKER

Same error.  Sub or Function not defined on "SysCmd".
Avatar of Senniger1
Senniger1

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

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!
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Senniger1
Senniger1

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.
sorry, just realized your codes are in excel, and i was insisting the codes that are used in access
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo