Access hangs

[Ronny Sigo]

Hello all,
I am trying to import the contents of an Excel sheet into my Access
Database. When clicking the button excel opens allright and does what I
programmed (the cells get updated with the value "Geimportee rd" ("Imported"
in English). So I get to the excel file alright but after changing the cells
to "Geimportee rd" Access hangs. When viewing the task manager it says that
the CPU activity is 100% and the only way to get out of this is the three
finger solution. I have putthe code below, can anybody help me with this?

Many thanks,

Ronny
*************** *************** *************** ****
Public Declare Function FindWindow Lib "user32" Alias "FindWindow A"
(ByVallpClassNa me As String, ByVal lpWindowName As String) As Long
Public Declare Function SendMessage Lib "user32.dll " Alias "SendMessag eA"
(ByVal hWnd As Long, ByVal Msg As Long, wParam As Any, lParam As Any) As
Long
*************** *************** *************** ****
Public Function DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLM AIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Function
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Function
*************** *************** *************** *****
Private Sub btnImportTGAFFG _Click()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunn ing As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Applicat ion")
If Err.Number <> 0 Then ExcelWasNotRunn ing = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

' Declare Integers with 'n' = the same name as the field to reference the
columns
Dim nGeslacht As Integer, nTaal As Integer, nAanspreking As Integer, nNaam
As Integer, nVoornaam As Integer, nStraat As Integer
Dim nHuisnr As Integer, nPostcode As Integer, nGemeente As Integer,
nProvincie As Integer, nLand As Integer, nEmailadres
Dim nUrl As Integer, nTelefoon As Integer, nGsm As Integer, nFax As Integer,
nNote As Integer, nRow As Integer

Dim sEmailadres As String

nGeslacht = 4
nTaal = 1
nAanspreking = 3
nNaam = 5
nVoornaam = 6
nStraat = 8
nHuisnr = 9
nPostcode = 10
nGemeente = 11
nProvincie = 12
nLand = 13
nEmailadres = 2
nUrl = 17
nTelefoon = 14
nGsm = 15
nFax = 16
nNote = 7

nRow = 3

sEmailadres = "X" 'Serves to start loop
' Show Microsoft Excel through its Application property. Dan
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.

MyXL.Applicatio n.Visible = True
MyXL.Parent.Win dows(1).Visible = True

'Set the object variable to reference the file you want to see.
Set MyXL = GetObject("MyPa th\Myfile.XLS")
'Do manipulations of the file here.

Dim db As ADODB.Recordset
Set db = CurrentProject. Connection.Exec ute("SELECT * " & "FROM tblDhscmails
") _
'& "WHERE Docname IS NOT NULL " _
'& "ORDER BY ID;")
Do While Nz(Trim(sEmaila dres)) <> ""
If Nz(Trim(MyXL.Ap plication.Cells (nRow, nEmailadres).Va lue)) <> "" Then
db.AddNew
db!Taal = Nz(Trim(MyXL.Ap plication.Cells (nRow, nTaal).Value))
MyXL.Applicatio n.Cells(nRow, nTaal).Value = "Geimportee rd!"
db!Geslacht = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nGeslacht).Valu e))
MyXL.Applicatio n.Cells(nRow, nGeslacht).Valu e = "Geimportee rd!"
db!Aanspreking = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nAanspreking).V alue))
MyXL.Applicatio n.Cells(nRow, nAanspreking).V alue = "Geimportee rd!"
db!Naam = Nz(Trim(MyXL.Ap plication.Cells (nRow, nNaam).Value))
MyXL.Applicatio n.Cells(nRow, nNaam).Value = "Geimportee rd!"
db!Voornaam = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nVoornaam).Valu e))
MyXL.Applicatio n.Cells(nRow, nVoornaam).Valu e = "Geimportee rd!"
db!Straat = Nz(Trim(MyXL.Ap plication.Cells (nRow, nStraat).Value) )
MyXL.Applicatio n.Cells(nRow, nStraat).Value = "Geimportee rd!"
db!Huisnr = Nz(Trim(MyXL.Ap plication.Cells (nRow, nHuisnr).Value) )
MyXL.Applicatio n.Cells(nRow, nHuisnr).Value = "Geimportee rd!"
db!Postcode = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nPostcode).Valu e))
MyXL.Applicatio n.Cells(nRow, nPostcode).Valu e = "Geimportee rd!"
db!Gemeente = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nGemeente).Valu e))
MyXL.Applicatio n.Cells(nRow, nGemeente).Valu e = "Geimportee rd!"
db!Provincie = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nProvincie).Val ue))
MyXL.Applicatio n.Cells(nRow, nProvincie).Val ue = "Geimportee rd!"
db!Emailadres = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nEmailadres).Va lue))
'Loop will continue until sEmailadres = ""
sEmailadres = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nEmailadres).Va lue))
MyXL.Applicatio n.Cells(nRow, nEmailadres).Va lue = "Geimportee rd!"
db!Telefoon = Nz(Trim(MyXL.Ap plication.Cells (nRow,
nTelefoon).Valu e))
MyXL.Applicatio n.Cells(nRow, nTelefoon).Valu e = "Geimportee rd!"
db!GSM = Nz(Trim(MyXL.Ap plication.Cells (nRow, nGsm).Value))
MyXL.Applicatio n.Cells(nRow, nGsm).Value = "Geimportee rd!"
db!Fax = Nz(Trim(MyXL.Ap plication.Cells (nRow, nFax).Value))
MyXL.Applicatio n.Cells(nRow, nFax).Value = "Geimportee rd!"
db!Note = Nz(Trim(MyXL.Ap plication.Cells (nRow, nNote).Value))
MyXL.Applicatio n.Cells(nRow, nNote).Value = "Geimportee rd!"
db.Update
End If
nRow = nRow + 1
Loop

' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunn ing = True Then
MyXL.Applicatio n.Quit
End If

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
db.Close
End Sub

[Salad]

Ronny Sigo wrote:

Hello all,
I am trying to import the contents of an Excel sheet into my Access
Database. When clicking the button excel opens allright and does what I
programmed (the cells get updated with the value "Geimportee rd" ("Imported"
in English). So I get to the excel file alright but after changing the cells
to "Geimportee rd" Access hangs. When viewing the task manager it says that
the CPU activity is 100% and the only way to get out of this is the three
finger solution. I have putthe code below, can anybody help me with this?

Many thanks,

I would open up your code and click on the bar to step through your code
to find out where it aborts. Or do something like this

db!Geslacht = Nz(Trim(...
MyXL.Applicatio n.Cells...
msgbox "Geslaccht"
db!Aanspreking = NZ(Trim(...
MyXL.Applicatio n.Cells(...
msgbox "Aansprekin g"

In other words, find out where it stops computing. Also, take out the
OnError statement if you have one.

Next, you are assigning the word "Imported" to each row/col as it is
imported. What if a column is set up as a date or number field?

Is it possible that you could set up a link between the Excel
spreadsheet and Access? That way the spreadsheet would simply be
another table in Access and could be updated either in Excell or in
Access. (File/GetExternalData/Link...set filetype to Excel, select
Excel file)