Close Excel then Open Form (2 Viewers)

[NascarBaritone]

Long story short: I have a macro that I run in Excel that copies, pastes, filters and then opens an Access database that I then want to import and produce a report.

When Access opens I want to get a file dialog asking what Excel file the user wants to import. However, I first want to close the active Excel window. The database opens with an Autoexec macro that calls the following module:

Option Compare Database
Function OpenDB() As String
Dim dlg As FileDialog
Dim StrFileName As String
DoCmd.RunCommand acCmdAppMinimize
    'Open Excel and make visible
Set o = GetObject(, "Excel.Application")
o.Visible = True
o.DisplayAlerts = False
o.Quit
o.DisplayAlerts = True
Set o = Nothing
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel CSV Files", "*.csv", 1
.Filters.Add "Excel Files", "*.xls", 2
.Filters.Add "All Files", "*.*", 3
If .Show = -1 Then
StrFileName = .SelectedItems(1)
DoCmd.TransferText acImportDelim, "tblCheckLog", StrFileName, True
DoCmd.OpenForm "FormDialog"
End If
End With
End Function

The file dialog opens, but the Excel application doesn't close. If I cancel the file dialog box THEN the Excel application closes. However, if shorten the code to just close Excel like below...it closes just fine.

Option Compare Database
Function OpenDB() As String
DoCmd.RunCommand acCmdAppMinimize
    'Open Excel and make visible
Set o = GetObject(, "Excel.Application")
o.Visible = True
o.DisplayAlerts = False
o.Quit
o.DisplayAlerts = True
Set o = Nothing
End Function

I have a feeling it is relatively obvious, but I can't seem to figure it out.

 

[boblarson]

I'm not understanding why you would need to open Excel from Access in order to select which Excel file to import.

 

[boblarson]

I'm not understanding why you would need to open Excel from Access in order to select which Excel file to import.

And why you would need to open Excel again from Access since the Excel file opened Access to begin with.

 

[NascarBaritone]

I left a line in the code that shouldn't have been there. All I am trying to do with this code is close Excel, not open it.

Here is the code without the "Open Excel and make visible" instruction:

Function OpenDB() As String
Dim dlg As FileDialog
Dim StrFileName As String
Set o = GetObject(, "Excel.Application")
o.Visible = True
o.DisplayAlerts = False
o.Quit
o.DisplayAlerts = True
Set o = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel CSV Files", "*.csv", 1
.Filters.Add "Excel Files", "*.xls", 2
.Filters.Add "All Files", "*.*", 3
If .Show = -1 Then
StrFileName = .SelectedItems(1)
DoCmd.TransferText acImportDelim, "tblCheckLog", StrFileName, True
DoCmd.OpenForm "FormDialog"
End If
End With
End Function

 

[boblarson]

I left a line in the code that shouldn't have been there. All I am trying to do with this code is close Excel, not open it.

But what you are doing is actually opening another instance of it instead of closing the one which opened Access. You don't want to have any of the CreateObject ("Excel.Application") stuff in there at all.

What you need, in your EXCEL code is to release Access so that Excel can close.

What is the current code in Excel which opens the Access database?

 

[NascarBaritone]

That actually just struck me. Here is the module that opens Access from Excel:

Global oApp As Object
Sub OpenAccess()
    Dim LPath As String
    'Path to Access database
    LPath = (ThisWorkbook.Path & "\Check Log Reporter.accdb")
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath
    
End Sub

I'm assuming I need to close Excel from here, but not quite sure how.

 

[boblarson]

You would use this:

Global oApp As Object
Sub OpenAccess()
    Dim LPath As String
    'Path to Access database
    LPath = (ThisWorkbook.Path & "\Check Log Reporter.accdb")
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath
 
[COLOR=red]  ' release Access and close Excel[/COLOR]
[COLOR=red]  oApp.UserControl = True[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000]  Set oApp = Nothing[/COLOR]
[COLOR=red]  Application.Quit[/COLOR]
End Sub

 

[NascarBaritone]

No luck. Let me walk you through my steps and see if there is something along the way that is causing an issue (or if there is a better way to go about it).

STEP 1:
User opens an Excel file (Check Log Review Template_MARCH ONLY.xls) that has the following Open code that checks to make sure there are no other Excel documents open:

Private Sub Workbook_Open()
If Workbooks.Count > 2 Then
MsgBox "Please close all other Excel documents before running reports.", vbInformation + vbOKOnly, "Close Excel Documents"
ActiveWorkbook.Close
Else
Call Macro1
End If
End Sub

STEP 2:
Macro1 is called. This opens another Excel file and scrubs it for the past week's entries, copies and pastes to a new document, and saves it as a .csv file:

Sub Macro1()
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\L104KLB\Desktop\2011DailyDirect$$InLog.xlsx"
    Range("A6:P5000").Select
    Selection.Copy
    Windows("Check Log Review Template_MARCH ONLY.xls").Activate
    ActiveSheet.Paste
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$P$5000").AutoFilter Field:=5, Criteria1:= _
        xlFilterLastWeek, Operator:=xlFilterDynamic
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Workbooks("2011DailyDirect$$InLog.xlsx").Close SaveChanges:=False
    ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\L104KLB\Desktop\Check Log Reporter\Check Log Report " & Format(Now(), "YYYYMMDDhhmmss") & ".csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    MsgBox "Last week's report has been saved.", vbInformation + vbOKOnly, "CSV File Created"
    Call OpenAccess
End Sub

STEP 3:
OpenAccess is called. This opens up Access (duh!):

Global oApp As Object
Sub OpenAccess()
    Dim LPath As String
    Dim objExcel As Excel.Application
    'Path to Access database
    LPath = (ThisWorkbook.Path & "\Check Log Reporter.accdb")
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath
 
  ' release Access and close Excel
  oApp.UserControl = True
 
  Set oApp = Nothing
  Application.DisplayAlerts = False
  Application.Quit
 
End Sub

STEP 4:
When Access opens my form (frmCheckLog) is automatically opened with the following on the Form_Open. The plan is to have the user import the .csv file that was just saved into a temporary table and then either save it as a PDF report (for future reference) or (most likely) print it off:

Private Sub Form_Open(Cancel As Integer)
Dim dlg As FileDialog
Dim StrFileName As String
DoCmd.RunCommand acCmdAppMinimize
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel CSV Files", "*.csv", 1
.Filters.Add "Excel Files", "*.xls", 2
.Filters.Add "All Files", "*.*", 3
If .Show = -1 Then
StrFileName = .SelectedItems(1)
DoCmd.TransferText acImportDelim, "tblCheckLog", StrFileName, True
DoCmd.OpenForm "FormDialog"
End If
End With
End Sub

Lengthy, I know, but it makes sense in my brain...except for the code apparently.

 

[boblarson]

This part:
Application.Quit

needs to go in Macro1 at the end.

 

[NascarBaritone]

Still no luck. Access opens, file dialog opens, but behind the Excel document (I have to click Access in the taskbar to see file dialog), and Excel doesn't close until I cancel the file dialog box.