I'm working in MS Access 2013 on Windows 10.
I'm trying to open an Excel file on disk, change the column formatting and some columns, save and quit.
The Sub runs, but after the .Quit
command, "EXCEL.EXE" keeps running and subsequent calls to that Sub will result in run-time errors.
If I close Access, "EXCEL.EXE" disappears on the Task Manager, but not if I do "Compact and Repair" the database.
I've another Sub that opens an Excel file on disk and alters the width of all columns to "auto-width", then closes, which doesn't leave "EXCEL.EXE" running.
I tried different line orders on Dims and Sets to Nothing, Workbook Closes, etc.
I searched here and on other sites. The only suggestions for VBA is to use something like ThisWorkbook.Saved = True
. I tried that before and after .Quit
to no effect.
Other than that, I only find solutions for VB.NET or other environments.
Sub changeXLcolumnFormatting()
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
XL.Workbooks.Open "C:\Users\640344\Desktop\rawDataTest.XLSX"
Dim sht As Worksheet
With XL
Set sht = ActiveWorkbook.Worksheets(1)
Dim rng As Range
Dim i As Integer, j As Integer
field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
sht.Select
end_of_table = sht.UsedRange.Columns.Count
For j = 0 To UBound(field_names)
For i = 1 To end_of_table
Set rng = sht.Cells(1, i)
If InStr(rng.Text, field_names(j)) > 0 Then
sht.Columns(i).NumberFormat = "yyyy-mm-dd HH:MM:ss"
End If
Next i
Next j
End With
Set rng = Nothing
Set sht = Nothing
XL.ActiveWorkbook.Close (True)
XL.Quit
Set XL = Nothing
End Sub
Restart the computer. 2. End Process to Winword.exe and Excel.exe in the task manager 3. Kill the process using taskkill.exe Although 1 is the easiest, it would also be time consuming to wait for the computer to start up again, launch all of the programs etc.
To close an Excel file, you need to use the “Close” method. With this method you can, specify if you want to save the changes or not. And, if you want to save and close a file that is not saved yet you can specify the path where you want to save it before closing.
To compile a workbook to an EXE you need to have the Microsoft Windows 7 or higher and Microsoft Office 2007 SP3 or higher with the latest service pack and updates installed on your computer. Our product supports all 32-bit and 64-bit versions of Excel from 2007 and higher, including Excel from Office 365.
In VBA, you can stop your macro execution manually with the Esc key or by pressing Ctrl+Break.
Declare and use a specific Workbook object - as you do for Worksheet and Range, like this:
Dim xls As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Range
Set xls = New Excel.Application
Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
Set wks = wkb.Worksheets(1)
Set rng = wks.Range(<something>)
' Do stuff.
wks.Name = "My New Name"
With rng
' Do more.
End With
wkb.Close True
Set rng = Nothing
Set wks = Nothing
Set wkb = Nothing
xls.Quit
Set xls = Nothing
Also, don't use Select, that's for visible use only. Define ranges instead.
Cinetyk's EDIT:
Using @Gustav 's indications, the code that does what I wanted and solves the problem is:
Sub changeXLcolumnFormatting()
Dim XL As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim rng As Range
Set XL = New Excel.Application
XL.Visible = False
XL.DisplayAlerts = False
Set wkb = XL.Workbooks.Open("C:\Users\640344\Desktop\rawDataTest.XLSX")
Set sht = wkb.Worksheets(1)
Dim i As Integer, j As Integer
field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
end_of_table = sht.UsedRange.Columns.Count
For j = 0 To UBound(field_names)
For i = 1 To end_of_table
Set rng = sht.Cells(1, i)
If InStr(rng.Text, field_names(j)) > 0 Then
sht.Columns(i).NumberFormat = "yyyy-mm-dd HH:MM:ss"
End If
Next i
Next j
wkb.Close (True)
Set rng = Nothing
Set sht = Nothing
XL.Quit
Set XL = Nothing
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With