title: Excel macro doesn't open on startup when called from MS Access
body: <p>I know this issue has been hashed over many times but mine isn't addressed there.
  I have an excel macro that is used for different projects. It's coded with variables, the variables are passed from MS Access and therefore, this macro can be used for any project.</p>
  <p>Not only it runs fine when double clicked, but it also works for other projects.
  The only thing I can think of that sets this project apart is that in the same procedure, another excel file is called a few steps before.
  But after run, that excel instance is closed (i checked, it really does close, i close it by</p>
  <pre><code>SET myexcelinstance = nothing 
  </code></pre>
  <p>What can it be? I can double click on the file for this project as well and everything is created without error.</p>
  <p>But when called from Ms Access, it just opens like a regular file</p>
  <p>edit: adding the macro</p>
  <p>this is in a separate module</p>
  <pre><code>Option Explicit
  
  Public Sub auto_open()
  MainProcedure
  End Sub
  </code></pre>
  <p>this is the main procedure, in a separate module</p>
  <pre><code>Option Explicit
  
  Public x      As Integer
  Public PadLength As Integer
  
  Public LastRow As Long
  Public LastRow4 As Long
  Public LastRow2 As Long
  
  Public CurPath As String
  
  Public ProjectName As String
  Public FormattedDate As String
  Public RunDate As Date
  Public ReportPath As String
  Public MonthlyPath As String
  
  Public TableName As String
  Public FinalExcelFileName As String
  Public ExcelFileName As String
  Public ExcelSheetName As String
  Public ExcelTemplate As String
  Public ExcelPasteTo As String
  Public TemplateFileName As String
  Public SheetToSelect As String
  
  Public FSO
  Public oShell As Object
  Public iResponse As Integer
  Public CurCell, CurRange As Range
  
  
  Public CurRowNum As Long
  Public LastRowOfSection As Long
  Public FirstRowOfSection As Long
  
  Public CurLastColumn As Variant
  Public CurLastRow As Long
    
  Public CurFileName As String
  Public CurSheetName As String
  
  Public Sub MainProcedure()
  
      Application.EnableCancelKey = xlDisabled
      Application.DisplayAlerts = False
      'Application.ScreenUpdating = False
      Application.EnableEvents = False
  
      CurPath = ActiveWorkbook.Path &amp; &quot;\&quot;
  
      'this is to deselect sheets
      Sheets(&quot;QFilesToExportEMail&quot;).Select
  
      Sheets(&quot;QReportDates&quot;).Activate
  
      FormattedDate = Range(&quot;A2&quot;).Value
      RunDate = Range(&quot;B2&quot;).Value
      ReportPath = Range(&quot;C2&quot;).Value
      MonthlyPath = Range(&quot;D2&quot;).Value
      ProjectName = Range(&quot;E2&quot;).Value
           
      Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
      Sheets(&quot;QFilesToExportEMail&quot;).Select
      'Ctrl + Shift + End
      LastRow = Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
      
  
      Dim i     As Integer
  
      CurRowNum = 2
  
      Set CurRange = Sheets(&quot;QFilesToExportEMail&quot;).Range(&quot;B&quot; &amp; CurRowNum &amp; &quot;:B&quot; &amp; LastRow) '''''sets the range to use
  
      For Each CurCell In CurRange                 ''''checks each cell in range
                       
          If CurCell &lt;&gt; &quot;&quot; Then                    '''''will only do something if the cell is not blank
                                     
              Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
              Sheets(&quot;QFilesToExportEMail&quot;).Select
                                     
              ExcelFileName = Range(&quot;B&quot; &amp; CurRowNum).Value
              FinalExcelFileName = Range(&quot;B&quot; &amp; CurRowNum).Value
              LastRowOfSection = Sheets(&quot;QFilesToExportEMail&quot;).Range(&quot;B&quot; &amp; CurRowNum &amp; &quot;:B&quot; &amp; LastRow).Find(what:=ExcelFileName, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
              'FirstRowOfSection = Sheets(&quot;QFilesToExportEMail&quot;).Range(&quot;B&quot; &amp; CurRowNum &amp; &quot;:B&quot; &amp; LastRow).Find(what:=ExcelFileName).Row
              TemplateFileName = Range(&quot;F&quot; &amp; CurRowNum).Value
                                     
              FirstRowOfSection = ActiveWorkbook.Worksheets(&quot;QFilesToExportEMail&quot;).Columns(2).Find(ExcelFileName).Row
  
              TableName = Range(&quot;A&quot; &amp; CurRowNum).Value
              ExcelSheetName = Range(&quot;C&quot; &amp; CurRowNum).Value
                                                          
              If ExcelSheetName = &quot;&quot; Then
                  ExcelSheetName = TableName
              End If
                                                          
              If CurRowNum = FirstRowOfSection Then
                  SheetToSelect = ExcelSheetName
              End If
                                     
              If IsNull(TemplateFileName) Or TemplateFileName = &quot;&quot; Then
                  Workbooks.Add
              Else
                  Workbooks.Open CurPath &amp; TemplateFileName
              End If
                                     
              ActiveWorkbook.SaveAs MonthlyPath &amp; FinalExcelFileName
                                     
              For i = CurRowNum To LastRowOfSection
                                                                   
                  Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
                  Sheets(&quot;QFilesToExportEMail&quot;).Select
                                                          
                  TableName = Range(&quot;A&quot; &amp; i).Value
                  ExcelSheetName = Range(&quot;C&quot; &amp; i).Value
                  ExcelTemplate = Range(&quot;D&quot; &amp; i).Value
                  ExcelPasteTo = Range(&quot;E&quot; &amp; i).Value
                                                          
                  If ExcelSheetName = &quot;&quot; Then
                      ExcelSheetName = TableName
                  End If
                                                          
                  If ExcelTemplate = &quot;format&quot; Then
                                                                        
                      Windows(FinalExcelFileName).Activate
                      ActiveWorkbook.Sheets.Add(after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = ExcelSheetName
                                                                                                                                              
                      Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
                      Sheets(TableName).Select
                                                                         
                      CurLastColumn = MyColumnLetter(Range(&quot;A1&quot;).CurrentRegion.Columns.Count)
                      CurLastRow = Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
                                                                        
                      Range(&quot;A1:&quot; &amp; CurLastColumn &amp; CurLastRow).Select
                      Selection.Copy
                                                                        
                      Windows(FinalExcelFileName).Activate
                      Sheets(ExcelSheetName).Select
                      Range(ExcelPasteTo).Select
                      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                                                                        
                      FormatBasics
                                                                        
                  ElseIf ExcelTemplate = &quot;&quot; Then
                                                                        
                      Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
                      Sheets(TableName).Select
                                                                        
                      CurLastColumn = MyColumnLetter(Range(&quot;A1&quot;).CurrentRegion.Columns.Count)
                      CurLastRow = Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
                                                                        
                      Range(&quot;A2:&quot; &amp; CurLastColumn &amp; CurLastRow).Select
                      Selection.Copy
                                                                        
                      Windows(FinalExcelFileName).Activate
                      Sheets(ExcelSheetName).Select
                      Range(ExcelPasteTo).Select
                      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                      '
                      '                                                                          Dim SearchString As String
                      '                                                                        Dim SearchRange As Range, cl As Range
                      '                                                                        Dim FirstFound As String
                      '                                                                        Dim sh As Worksheet
                      '
                      '                                                                        ' Set Search value
                      '                                                                        SearchString = &quot;N/A&quot;
                      '                                                                        Application.FindFormat.Clear
                      '                                                                        ' loop through all sheets
                      '                                                                        For Each sh In ActiveWorkbook.Worksheets
                      '                                                                            ' Find first instance on sheet
                      '                                                                            Set cl = sh.Cells.Find(What:=SearchString, _
                      '                                                                                After:=sh.Cells(1, 1), _
                      '                                                                                LookIn:=xlValues, _
                      '                                                                                LookAt:=xlPart, _
                      '                                                                                SearchOrder:=xlByRows, _
                      '                                                                                SearchDirection:=xlNext, _
                      '                                                                                MatchCase:=False, _
                      '                                                                                SearchFormat:=False)
                      '                                                                            If Not cl Is Nothing Then
                      '                                                                                ' if found, remember location
                      '                                                                                FirstFound = cl.Address
                      '                                                                                ' format found cell
                      '                                                                                Do
                      '                                                                                    cl.Font = &quot;Calibri&quot;
                      '                                                                                    cl.Interior.ColorIndex = 3
                      '                                                                                    ' find next instance
                      '                                                                                    Set cl = sh.Cells.FindNext(After:=cl)
                      '                                                                                    ' repeat until back where we started
                      '                                                                                Loop Until FirstFound = cl.Address
                      '                                                                            End If
                      '                                                                        Next
                                                                        
                                                                        
                  ElseIf ExcelTemplate Like &quot;*TEMPLATE*&quot; Then
                                                                        
                      Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
                                                                        
                      Sheets(ExcelTemplate).Copy after:=Workbooks(FinalExcelFileName).Sheets(1)
                      ActiveSheet.Name = ExcelSheetName
                      ActiveSheet.Move after:=Worksheets(Worksheets.Count) 'moves it to the end
                                                                                                                                              
                      Sheets(ExcelSheetName).Select
                                                                        
                      Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
                      Sheets(TableName).Select
                                                                        
                      CurLastColumn = MyColumnLetter(Range(&quot;A1&quot;).CurrentRegion.Columns.Count)
                      CurLastRow = Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
                                                                        
                      Range(&quot;A2:&quot; &amp; CurLastColumn &amp; CurLastRow).Select
                      Selection.Copy
                                                                        
                      Windows(FinalExcelFileName).Activate
                      Sheets(ExcelSheetName).Select
                      Range(ExcelPasteTo).Select
                      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                                                                        
                  End If
                                                          
                  Range(ExcelPasteTo).Select
                                                          
              Next i
                                                                   
              If LastRowOfSection &lt; LastRow Then
                  CurRowNum = LastRowOfSection + 1
              Else
                  CurRowNum = LastRowOfSection
              End If
                                              
          End If
                       
          Windows(FinalExcelFileName).Activate
                       
          If CheckSheet(&quot;Sheet1&quot;) Then
              Sheets(&quot;Sheet1&quot;).Delete
          End If
                       
          Sheets(SheetToSelect).Select
                                     
          ActiveWorkbook.Save
          ActiveWorkbook.Close
                       
          If LastRowOfSection &gt;= LastRow Then
              Exit For
          End If
                       
      Next
  
      CurSheetName = &quot;&quot;
  
      Windows(ProjectName &amp; &quot;.xlsm&quot;).Activate
      Sheets(&quot;QFilesToExportEMail&quot;).Select
      'Ctrl + Shift + End
      LastRow = Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
  
  
      Set CurRange = Sheets(&quot;QFilesToExportEMail&quot;).Range(&quot;A2:A&quot; &amp; LastRow) '''''sets the range to use
      For Each CurCell In CurRange                 ''''checks each cell in range
          If CurCell &lt;&gt; &quot;&quot; Then                    '''''will only do something if the cell is not blank
  
              CurSheetName = CurCell
  
              If CheckSheet(CurSheetName) Then
                  Sheets(CurSheetName).Delete
              End If
  
          End If
      Next
     
      Sheets(&quot;QFilesToExportEMail&quot;).Delete
      Sheets(&quot;QReportDates&quot;).Delete
                                               
      ActiveWorkbook.Save
      ActiveWorkbook.Close
  
  End Sub
  
  Function CheckSheet(ByVal sCurSheetName As String) As Boolean
  
      Dim oSheet As Excel.Worksheet
      Dim bReturn As Boolean
  
      For Each oSheet In ActiveWorkbook.Sheets
          If oSheet.Name = sCurSheetName Then
              bReturn = True
              Exit For
          End If
      Next oSheet
  
      CheckSheet = bReturn
  
  End Function
  
  Public Function MyColumnLetter(MyNumber As Long) As String
      If MyNumber &gt; 26 Then
          MyColumnLetter = Chr(Int((MyNumber - 1) / 26) + 64) &amp; Chr(((MyNumber - 1) Mod 26) + 65)
      Else
          MyColumnLetter = Chr(MyNumber + 64)
      End If
  End Function
  
  Public Function xlLastCol(Optional WorkCurSheetName As String) As Long 'finds the last populated col in a worksheet
      If WorkCurSheetName = vbNullString Then WorkCurSheetName = ActiveSheet.Name
      With Worksheets(WorkCurSheetName)
          On Error Resume Next
          xlLastCol = .Cells.Find(&quot;*&quot;, .Cells(1), xlFormulas, xlWhole, xlByColumns, xlPrevious).Column
          If Err &lt;&gt; 0 Then xlLastCol = 0
      End With
  End Function
  
  Public Function QueryDB(ByVal connectionString As String, ByVal Target As Excel.Range, ByVal SQL As String)
      Dim qt    As Excel.QueryTable
      Dim ws    As Excel.Worksheet
      Set ws = Target.Parent
      Set qt = ws.QueryTables.Add(connectionString, Target, SQL)
      qt.Refresh BackgroundQuery:=False
  End Function
  
  '=====================================================================
  'The following function will left pad a string with a specified
  'character. It accepts a base string which is to be left padded with
  'characters, a character to be used as the pad character, and a
  'length which specifies the total length of the padded result.
  '=====================================================================
  Function Lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
  
      PadLength = MyPaddedLength - Len(MyValue)
      Dim PadString As String
      For x = 1 To PadLength
          PadString = PadString &amp; MyPadCharacter
      Next
      Lpad = PadString + MyValue
  
  End Function
  
  '=====================================================================
  'The following function will right pad a string with a specified
  'character. It accepts a base string which is to be right padded with
  'characters, a character to be used as the pad character, and a
  'length which specifies the total length of the padded result.
  '=====================================================================
  Function Rpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
  
      PadLength = MyPaddedLength - Len(MyValue)
      Dim PadString As String
      For x = 1 To PadLength
          PadString = MyPadCharacter &amp; PadString
      Next
      Rpad = MyValue + PadString
  
  End Function
  
  Public Function FormatBasics()
  
      Dim x     As Long
      Dim FormatColumnLetter, FormatColumnName As String
  
      Application.EnableCancelKey = xlDisabled
  
      Cells.Select
      With Selection.Font
          .Name = &quot;Calibri&quot;
          .Size = 11
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .ColorIndex = 1
          .TintAndShade = 0
          .ThemeFont = xlThemeFontMinor
      End With
      
      Range(&quot;A1:&quot; &amp; MyColumnLetter(xlLastCol) &amp; &quot;1&quot;).Select
      
      Selection.Font.Bold = True
      
      With Selection.Interior
          .PatternColorIndex = 2
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = -0.149998474074526
          .PatternTintAndShade = 0
      End With
      
      Selection.AutoFilter
      
      Rows(&quot;2:2&quot;).Select
      ActiveWindow.FreezePanes = True
           
           
      For x = 1 To xlLastCol
           
          FormatColumnLetter = MyColumnLetter(x)
          FormatColumnName = StrConv(Range(FormatColumnLetter &amp; &quot;1&quot;).Value, vbLowerCase)
           
          Range(FormatColumnLetter &amp; &quot;1&quot;).Value = StrConv(Range(FormatColumnLetter &amp; &quot;1&quot;).Value, vbProperCase)
  
          If FormatColumnName = &quot;factypedesc&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;FacTypeDesc&quot;
          ElseIf FormatColumnName = &quot;facsubtypedesc&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;FacSubTypeDesc&quot;
          ElseIf FormatColumnName = &quot;facsubtype&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;FacSubType&quot;
          ElseIf FormatColumnName = &quot;mpinetworkcode&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;Network&quot;
          ElseIf FormatColumnName = &quot;mpicontractid&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;ContractNumber&quot;
          ElseIf FormatColumnName = &quot;dob&quot; Or FormatColumnName = &quot;dateofbirth&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;DOB&quot;
          ElseIf FormatColumnName = &quot;deanumber&quot; Then
              Range(FormatColumnLetter &amp; &quot;1&quot;).Value = &quot;DEANumber&quot;
          End If     
            
           
      Next x
      
      Cells.Select
      Cells.EntireColumn.AutoFit
  
      Columns(&quot;A:&quot; &amp; MyColumnLetter(xlLastCol)).Sort key1:=Range(&quot;A2&quot;), order1:=xlAscending, Header:=xlYes
  
      Range(&quot;A2&quot;).Select
  
  End Function
  </code></pre>