Hello,

I have some VBA code in which I changing dates from Euro style to U.S.
From MS Access I create an Excel Object and change the dates. I then
quit but it still hangs around. Any clues?

Option Explicit
Sub ConvertAllDates()


DoCmd.SetWarnings False

Dim appShipments As Excel.Application
Set appShipments = CreateObject(Class:="Excel.application")
appShipments.Workbooks.Open Filename:="myfilename.xls"


Dim shtShipments As Worksheet
Set shtShipments =
appShipments.Workbooks(1).Worksheets("WorkSheetNam e
Dim CelNum As Integer
Dim thisCol As String, newdate As Variant
Dim THIScol2 As String
Dim thiscol3 As String
thisCol = "C"
THIScol2 = "G"
thiscol3 = "H"
Dim IntRowCount As Integer
IntRowCount =
appShipments.ActiveSheet.Range("c1").CurrentRegion .Rows.Count
For CelNum = 1 To IntRowCount
newdate = ConvertDate(Cells(CelNum, thisCol))
newdate = ConvertDate(Cells(CelNum, THIScol2))
newdate = ConvertDate(Cells(CelNum, thiscol3))
If newdate < 0 Then
Cells(CelNum, thisCol).Value = newdate
Cells(CelNum, THIScol2).Value = newdate
Cells(CelNum, thiscol3).Value = newdate
End If
Next

appShipments.Workbooks("myfilename.xls").Save
appShipments.Workbooks("myfilename.xls").Close
appShipments.Quit
Set appShipments = Nothing

DoCmd.OpenTable tablename:="Shipments"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.Close acTable, objectname:="Shipments"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, _
tablename:="Shipments", _
Filename:="myfilename.xls", _
hasfieldnames:=True
DoCmd.SetWarnings True


End Sub
Function ConvertDate(sDate As String) As Date
Dim mth As Integer, yr As Integer, dy As Integer
On Error GoTo trap
mth = CInt(Mid(sDate, 4, 2))
yr = CInt(Right(sDate, 2))
dy = CInt(Left(sDate, 2))
ConvertDate = DateSerial(yr, mth, dy)
Exit Function
trap:
'MsgBox Error
Err.Clear
ConvertDate = 0
End Function

Orphan references are the most likely cause.
See my comments below. Also, it would be safer to use
a Long data type for an Excel row reference rather than an Integer.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Here are some general guidelines to use when automating Excel...

1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add ' or open
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=True 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.

'------------------------------------------------------------

"Intui_Sol"
wrote in message
Hello,
I have some VBA code in which I changing dates from Euro style to U.S.
From MS Access I create an Excel Object and change the dates. I then
quit but it still hangs around. Any clues?

Option Explicit
Sub ConvertAllDates()
DoCmd.SetWarnings False
Dim appShipments As Excel.Application
Set appShipments = CreateObject(Class:="Excel.application")
appShipments.Workbooks.Open Filename:="myfilename.xls"
Dim shtShipments As Worksheet
Set shtShipments = appShipments.Workbooks(1).Worksheets("WorkSheetNam e") '***
Dim CelNum As Integer
Dim thisCol As String, newdate As Variant
Dim THIScol2 As String
Dim thiscol3 As String
thisCol = "C"
THIScol2 = "G"
thiscol3 = "H"
Dim IntRowCount As Integer
IntRowCount =
appShipments.ActiveSheet.Range("c1").CurrentRegion .Rows.Count
For CelNum = 1 To IntRowCount
newdate = ConvertDate(Cells(CelNum, thisCol))
newdate = ConvertDate(Cells(CelNum, THIScol2))
newdate = ConvertDate(Cells(CelNum, thiscol3))
If newdate < 0 Then
Cells(CelNum, thisCol).Value = newdate
Cells(CelNum, THIScol2).Value = newdate
Cells(CelNum, thiscol3).Value = newdate
End If
Next
appShipments.Workbooks("myfilename.xls").Save
appShipments.Workbooks("myfilename.xls").Close
appShipments.Quit
Set appShipments = Nothing
DoCmd.OpenTable tablename:="Shipments"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.Close acTable, objectname:="Shipments"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, _
tablename:="Shipments", _
Filename:="myfilename.xls", _
hasfieldnames:=True
DoCmd.SetWarnings True
End Sub

Function ConvertDate(sDate As String) As Date
Dim mth As Integer, yr As Integer, dy As Integer
On Error GoTo trap
mth = CInt(Mid(sDate, 4, 2))
yr = CInt(Right(sDate, 2))
dy = CInt(Left(sDate, 2))
ConvertDate = DateSerial(yr, mth, dy)
Exit Function
trap:
'MsgBox Error
Err.Clear
ConvertDate = 0
End Function

Thanks a lot- need to be more specific in my ranges!



Jim Cone wrote:
Orphan references are the most likely cause.
See my comments below. Also, it would be safer to use
a Long data type for an Excel row reference rather than an Integer.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Here are some general guidelines to use when automating Excel...

1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add ' or open
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=True 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.

'------------------------------------------------------------

"Intui_Sol"
wrote in message
Hello,
I have some VBA code in which I changing dates from Euro style to U.S.
From MS Access I create an Excel Object and change the dates. I then
quit but it still hangs around. Any clues?

Option Explicit
Sub ConvertAllDates()
DoCmd.SetWarnings False
Dim appShipments As Excel.Application
Set appShipments = CreateObject(Class:="Excel.application")
appShipments.Workbooks.Open Filename:="myfilename.xls"
Dim shtShipments As Worksheet
Set shtShipments = appShipments.Workbooks(1).Worksheets("WorkSheetNam e") '***
Dim CelNum As Integer
Dim thisCol As String, newdate As Variant
Dim THIScol2 As String
Dim thiscol3 As String
thisCol = "C"
THIScol2 = "G"
thiscol3 = "H"
Dim IntRowCount As Integer
IntRowCount =
appShipments.ActiveSheet.Range("c1").CurrentRegion .Rows.Count
For CelNum = 1 To IntRowCount
newdate = ConvertDate(Cells(CelNum, thisCol))
newdate = ConvertDate(Cells(CelNum, THIScol2))
newdate = ConvertDate(Cells(CelNum, thiscol3))
If newdate < 0 Then
Cells(CelNum, thisCol).Value = newdate
Cells(CelNum, THIScol2).Value = newdate
Cells(CelNum, thiscol3).Value = newdate
End If
Next
appShipments.Workbooks("myfilename.xls").Save
appShipments.Workbooks("myfilename.xls").Close
appShipments.Quit
Set appShipments = Nothing
DoCmd.OpenTable tablename:="Shipments"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.Close acTable, objectname:="Shipments"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, _
tablename:="Shipments", _
Filename:="myfilename.xls", _
hasfieldnames:=True
DoCmd.SetWarnings True
End Sub

Function ConvertDate(sDate As String) As Date
Dim mth As Integer, yr As Integer, dy As Integer
On Error GoTo trap
mth = CInt(Mid(sDate, 4, 2))
yr = CInt(Right(sDate, 2))
dy = CInt(Left(sDate, 2))
ConvertDate = DateSerial(yr, mth, dy)
Exit Function
trap:
'MsgBox Error
Err.Clear
ConvertDate = 0
End Function