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 "Geimporteerd" ("Imported"
in English). So I get to the excel file alright but after changing the cells
to "Geimporteerd" 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 "FindWindowA"
(ByVallpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA"
(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("XLMAIN", 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 ExcelWasNotRunning 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.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = 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.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
'Set the object variable to reference the file you want to see.
Set MyXL = GetObject("MyPath\Myfile.XLS")
'Do manipulations of the file here.
Dim db As ADODB.Recordset
Set db = CurrentProject.Connection.Execute("SELECT * " & "FROM tblDhscmails
") _
'& "WHERE Docname IS NOT NULL " _
'& "ORDER BY ID;")
Do While Nz(Trim(sEmailadres)) <> ""
If Nz(Trim(MyXL.Application.Cells(nRow, nEmailadres).Value)) <> "" Then
db.AddNew
db!Taal = Nz(Trim(MyXL.Application.Cells(nRow, nTaal).Value))
MyXL.Application.Cells(nRow, nTaal).Value = "Geimporteerd!"
db!Geslacht = Nz(Trim(MyXL.Application.Cells(nRow,
nGeslacht).Value))
MyXL.Application.Cells(nRow, nGeslacht).Value = "Geimporteerd!"
db!Aanspreking = Nz(Trim(MyXL.Application.Cells(nRow,
nAanspreking).Value))
MyXL.Application.Cells(nRow, nAanspreking).Value = "Geimporteerd!"
db!Naam = Nz(Trim(MyXL.Application.Cells(nRow, nNaam).Value))
MyXL.Application.Cells(nRow, nNaam).Value = "Geimporteerd!"
db!Voornaam = Nz(Trim(MyXL.Application.Cells(nRow,
nVoornaam).Value))
MyXL.Application.Cells(nRow, nVoornaam).Value = "Geimporteerd!"
db!Straat = Nz(Trim(MyXL.Application.Cells(nRow, nStraat).Value))
MyXL.Application.Cells(nRow, nStraat).Value = "Geimporteerd!"
db!Huisnr = Nz(Trim(MyXL.Application.Cells(nRow, nHuisnr).Value))
MyXL.Application.Cells(nRow, nHuisnr).Value = "Geimporteerd!"
db!Postcode = Nz(Trim(MyXL.Application.Cells(nRow,
nPostcode).Value))
MyXL.Application.Cells(nRow, nPostcode).Value = "Geimporteerd!"
db!Gemeente = Nz(Trim(MyXL.Application.Cells(nRow,
nGemeente).Value))
MyXL.Application.Cells(nRow, nGemeente).Value = "Geimporteerd!"
db!Provincie = Nz(Trim(MyXL.Application.Cells(nRow,
nProvincie).Value))
MyXL.Application.Cells(nRow, nProvincie).Value = "Geimporteerd!"
db!Emailadres = Nz(Trim(MyXL.Application.Cells(nRow,
nEmailadres).Value))
'Loop will continue until sEmailadres = ""
sEmailadres = Nz(Trim(MyXL.Application.Cells(nRow,
nEmailadres).Value))
MyXL.Application.Cells(nRow, nEmailadres).Value = "Geimporteerd!"
db!Telefoon = Nz(Trim(MyXL.Application.Cells(nRow,
nTelefoon).Value))
MyXL.Application.Cells(nRow, nTelefoon).Value = "Geimporteerd!"
db!GSM = Nz(Trim(MyXL.Application.Cells(nRow, nGsm).Value))
MyXL.Application.Cells(nRow, nGsm).Value = "Geimporteerd!"
db!Fax = Nz(Trim(MyXL.Application.Cells(nRow, nFax).Value))
MyXL.Application.Cells(nRow, nFax).Value = "Geimporteerd!"
db!Note = Nz(Trim(MyXL.Application.Cells(nRow, nNote).Value))
MyXL.Application.Cells(nRow, nNote).Value = "Geimporteerd!"
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 ExcelWasNotRunning = True Then
MyXL.Application.Quit
End If
Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
db.Close
End Sub
