By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Access hangs

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 "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,


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.Application.Cells...
msgbox "Geslaccht"
db!Aanspreking = NZ(Trim(...
MyXL.Application.Cells(...
msgbox "Aanspreking"

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)
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.