Odd Problem with Excel Macro

[pgrmdave]

So, I have a macro that formats an excel file, saves it, inputs it to an access table, and prints three reports based on it. It works like a charm, with one exception. For some reason it seems to keep excel open after I've closed it. Here's the code:

 

Sub Rename_Columns()
'
' Rename_Columns Macro
' Macro recorded 11/20/2008 by David Rice - rename the headers and print
'

'
   Dim Rpath As String, ReturnValue As Double, oApp As Object
   Dim accessName As String, excelName As String
   
   
   'AMEXpath is the path to the folder with the data
   AMEXpath = "C:Reports"
   
   'accessName is the name of the access file to be created
   'excelname is the name of the excel file to be created
   accessName = "Reports.mdb"
   excelName = "ExcelData.xls"
   
   Application.ScreenUpdating = False
   
   'Changes the different column names
   Range("A1").Select
   ActiveCell.FormulaR1C1 = "Name"
   Range("B1").Select
   ActiveCell.FormulaR1C1 = "T Date"
   Range("C1").Select
   ActiveCell.FormulaR1C1 = "B Date"
   Range("D1").Select
   ActiveCell.FormulaR1C1 = "A"
   Range("E1").Select
   ActiveCell.FormulaR1C1 = "C"
   Range("F1").Select
   ActiveCell.FormulaR1C1 = "T S"
   Range("G1").Select
   ActiveCell.FormulaR1C1 = "M Name"
   
   'Creates a list of the data
   ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$G"), , xlYes).Name = "List1"
   
   'Selects the last cell in column A ("report generated on X/XX/XX") and deletes it
   Range("A65536").End(xlUp).Select
   ActiveCell.FormulaR1C1 = ""
   
   'Autofits the cells to make it easier to read
   Cells.Select
   Cells.EntireColumn.AutoFit
   
   'Sorts by name
   Range("A1").Select
   Columns("A:G").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
       xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal
   
   'Saves into the current path as 'excelname'
   ActiveWorkbook.SaveAs Filename:= _
       ActiveWorkbook.Path & "" & excelName, FileFormat _
       :=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
       False, CreateBackup:=False
       
   'Opens Microsoft Access
   Set oApp = CreateObject("Access.Application")
   With oApp
       .Visible = False 'Makes it invisible
       .OpenCurrentDatabase Rpath & accessName 'Opens the database at Rpath & accessname
       .DoCmd.DeleteObject acTable, "Long" 'Deletes the table that was there
       .DoCmd.TransferSpreadsheet acImport, , "Long", ActiveWorkbook.Path & "" & excelName, True 'Imports the spreadsheet into a new table: "Long"
       .DoCmd.OpenReport "A" 'Opens and immediately prints the three reports
       .DoCmd.OpenReport "B"
       .DoCmd.OpenReport "C"
       .DoCmd.Quit 'Closes access
   End With
   Application.ScreenUpdating = True
   
End Sub

 

After I run it and close excel, I often get a message that tells me that ExcelData is ready for read/write and it asks me to continue or cancel. Either way, an excel screen comes up without screenupdating, and I have to close it. Even when that message doesn't appear, if I go into the task manager I can see that excel is still running. What's going on here? Does excel automatically open a file if it saves as, and if so, how can I close it?

[alexander]

i dont think its a problem with excel save as function opens another excel window, of anything that could make sense, it should be an explorer instance, not excel.

 

i might not be getting you right here, but are you saying that excel is supposed to close or simething, because i can't seem to find the code that closes excel...

 

here's a reference i'm using: Excel FAQ - Macros

[maddog]

So, I have a macro that formats an excel file, saves it, inputs it to an access table, and prints three reports based on it. It works like a charm, with one exception. For some reason it seems to keep excel open after I've closed it. Here's the code:

 

Sub Rename_Columns()
'
' Rename_Columns Macro
' Macro recorded 11/20/2008 by David Rice - rename the headers and print
'

'
   Dim Rpath As String, ReturnValue As Double, oApp As Object
   Dim accessName As String, excelName As String
   
   
   'AMEXpath is the path to the folder with the data
   AMEXpath = "C:Reports"
   
   'accessName is the name of the access file to be created
   'excelname is the name of the excel file to be created
   accessName = "Reports.mdb"
   excelName = "ExcelData.xls"
   
   Application.ScreenUpdating = False
   
   'Changes the different column names
   Range("A1").Select
   ActiveCell.FormulaR1C1 = "Name"
   Range("B1").Select
   ActiveCell.FormulaR1C1 = "T Date"
   Range("C1").Select
   ActiveCell.FormulaR1C1 = "B Date"
   Range("D1").Select
   ActiveCell.FormulaR1C1 = "A"
   Range("E1").Select
   ActiveCell.FormulaR1C1 = "C"
   Range("F1").Select
   ActiveCell.FormulaR1C1 = "T S"
   Range("G1").Select
   ActiveCell.FormulaR1C1 = "M Name"
   
   'Creates a list of the data
   ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$G"), , xlYes).Name = "List1"
   
   'Selects the last cell in column A ("report generated on X/XX/XX") and deletes it
   Range("A65536").End(xlUp).Select
   ActiveCell.FormulaR1C1 = ""
   
   'Autofits the cells to make it easier to read
   Cells.Select
   Cells.EntireColumn.AutoFit
   
   'Sorts by name
   Range("A1").Select
   Columns("A:G").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
       xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal
   
   'Saves into the current path as 'excelname'
   ActiveWorkbook.SaveAs Filename:= _
       ActiveWorkbook.Path & "" & excelName, FileFormat _
       :=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
       False, CreateBackup:=False
       
   'Opens Microsoft Access
   Set oApp = CreateObject("Access.Application")
   With oApp
       .Visible = False 'Makes it invisible
       .OpenCurrentDatabase Rpath & accessName 'Opens the database at Rpath & accessname
       .DoCmd.DeleteObject acTable, "Long" 'Deletes the table that was there
       .DoCmd.TransferSpreadsheet acImport, , "Long", ActiveWorkbook.Path & "" & excelName, True 'Imports the spreadsheet into a new table: "Long"
       .DoCmd.OpenReport "A" 'Opens and immediately prints the three reports
       .DoCmd.OpenReport "B"
       .DoCmd.OpenReport "C"
       .DoCmd.Quit 'Closes access
   End With
   Application.ScreenUpdating = True
   
End Sub

 

After I run it and close excel, I often get a message that tells me that ExcelData is ready for read/write and it asks me to continue or cancel. Either way, an excel screen comes up without screenupdating, and I have to close it. Even when that message doesn't appear, if I go into the task manager I can see that excel is still running. What's going on here? Does excel automatically open a file if it saves as, and if so, how can I close it?

 

pgrmdave,

 

I wasn't sure I caught the end of your program.

 

One universal method I know (assuming you Really want to do this) is to do the following

 

[font=Courier New][color=Blue]Application.exit
[/color][/font]

 

You would put it just before the "End Sub". If you have not changed your current workbook you would Immediately Exit.

 

maddog