Hello all,
As you may recognize in the following code i've tried to merge 2 example pieces of code into one. I'm getting an error message stating an object is required on the line; -
For lngColumn =0 to rst.Fields.Count -1
-
Complete code is as follows; -
-
Sub tellijstvest()
-
Dim qdf As DAO.QueryDef
-
Dim dbs As DAO.Database
-
Dim rstTel As DAO.Recordset
-
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
-
' *** Aantal variabelen worden voorbereid om gegevens te bewaren.
-
Dim strTemp As String, strSQL As String, strTel As String, strBestandspad As String, strWorksheetnaam As String, strVestiging As String
-
' *** Aantal
-
' *** De tijdelijke query wordt zExport genoemd.
-
Const strTempQueryName As String = "zExportTellijst"
-
' *** Variabele dbs wordt gevuld met de gegevens van de huidige database
-
Set dbs = CurrentDb
-
' *** Variabele strVestiging wordt hier gevuld, door dit op te vragen bij de gebruiker.
-
strVestiging = InputBox(Prompt:="Voor welke vestiging?", _
-
Title:="Vestigingsnummer")
-
' *** Eventueel kan hier een if functie ingevoegd worden om alle vestigingen te doen als het veld leeggelaten wordt.
-
' *** Het bestand wordt aangemaakt op de volgende locatie, met vestigingsnummer gevolgd door datum in de bestandsnaam.
-
strBestandspad = "C:\test\" & strVestiging & Format(Now(), "yyyMMMdd") & ".xls"
-
-
' *** onbekende code om de query te initialiseren
-
strTemp = dbs.TableDefs(0).Name
-
strSQL = "SELECT * FROM [" & strTemp & "] where 1=0;"
-
Set qdf = dbs.CreateQueryDef(strTempQueryName, strSQL)
-
qdf.Close
-
strTemp = strTempQueryName
-
-
' *** alle regels en kolommen met het betreffende vestigingsnummer worden in een recordset opgeslagen
-
strSQL = "SELECT * from tbl_dbs_test WHERE " & "Ves = " & strVestiging & ";"
-
Set rstTel = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
-
' *** de eerste regel bevat kolomkoppen
-
blnHeaderRow = True
-
-
'hier wordt een excel bestand gemaakt
-
On Error Resume Next
-
Set xlx = GetObject(, "Excel.Application")
-
If Err.Number <> 0 Then
-
Set xlx = CreateObject("Excel.Application")
-
blnEXCEL = True
-
End If
-
Err.Clear
-
On Error GoTo 0
-
xlx.Visible = False
-
' *** Nieuw werkboek wordt gemaakt, met het vestigingsnummer als naam.
-
Set xlw = xlx.workbooks.Add
-
Set xls = xlw.worksheets(1)
-
xls.Name = strVestiging
-
' *** Excel begint in veld A1
-
Set xlc = xls.range("A1")
-
' *** als er gegevens in de recordset staan worden deze naar excel geschreven
-
If rstTel.EOF = False And rstTel.BOF = False Then
-
' *** eerst de header
-
For lngColumn = 0 To rst.Fields.Count - 1
-
xlc.offset(0, lngColumn).Value = rstTel.Fields(lngColumn).Name
-
' *** door naar de volgende
-
Next lngColumn
-
Set xlc = xlc.offset(1, 0)
-
xl.copyfromrecordset rstTel
-
End If
-
rstTel.Close
-
Set rstTel = Nothing
-
dbs.Close
-
Set xlc = Nothing
-
Set xls = Nothing
-
xlw.saveas strBestandspad
-
xlw.Close False
-
Set xlw = Nothing
-
If blnEXCEL = True Then xlx.Quit
-
Set xlx = Nothing
-
-
End Sub
-
Anyone know why im getting this error? I'm running access 2003 and Excel 2003.
Kind regards,
Oxydo
It is easy to miss names that are not what you have defined - and that at least appears to be what is wrong in this case.
The recordset object variable you have defined and used everywhere else except line 53 is rstTel, but in line 53 you are referring to the non-existent variable rst - hence the error.
The VBA compiler would have warned you about variable rst not being defined if you had an option explicit statement at the top of your module. If you are not in the habit of doing so it is best to have such a statement in all modules, as the compiler then explicitly requires that all variable names have been defined in Dim, Public, Private or Static statements before they are used.
-Stewart
2 2397
It is easy to miss names that are not what you have defined - and that at least appears to be what is wrong in this case.
The recordset object variable you have defined and used everywhere else except line 53 is rstTel, but in line 53 you are referring to the non-existent variable rst - hence the error.
The VBA compiler would have warned you about variable rst not being defined if you had an option explicit statement at the top of your module. If you are not in the habit of doing so it is best to have such a statement in all modules, as the compiler then explicitly requires that all variable names have been defined in Dim, Public, Private or Static statements before they are used.
-Stewart
Stewart,
Thanks for your solution as well as your tip to use option explicit. I've now added this rule and found & solved a few more issues with this sub, thanks for the pointer!
Kind regards,
Oxydo
Sign in to post your reply or Sign up for a free account.
Similar topics
by: News Groups |
last post by:
Dear Group
I am trying to use the following code to refer to a form and control on my
form. I am supposed to be using VBSCRIPT for this code.
I always get an "object required: 'document' " error:...
|
by: contact |
last post by:
Hi, I'm trying to use the following to replace a text string in all the
links on a page:
var arrLinks = document.links;
for(var LinkIterator = 0; LinkIterator < arrLinks.length;
LinkIterator++)...
|
by: google |
last post by:
I am trying to use the following ASP code to examine the file names in
a folder:
Dim fso, f, fl, s, fs
Set fso = CreateObject("Scripting.FileSystemObject")
Set f =...
|
by: RICHARD BROMBERG |
last post by:
I am using Access 200.
I have a form which includes text boxes L1, L2 and L3
And a table tblGrades with number fields L1, L2 and L3
|
by: formatt |
last post by:
Hi can anyone help with this little problem. I have a simple script to
swap between two divs on a page. It works perfectly in Firefox & Safari
& Opera. It fails in IE 6 giving Object Required...
|
by: tcnjdeluca |
last post by:
Hello I am sorry if this is a really novice question, but I am virtually clueless when it comes to Javascript. I have a page that is running the "leightbox" script. I have modified the script as...
|
by: jatin32 |
last post by:
Hi,
I have below code on Form_Load() , I get one time error when I open this form.
424 Object required, please help ASAP.
Option Compare Database
Private Sub Form_Load()
|
by: ash2009 |
last post by:
Hi,
I am getting an object required error on IE6
Line: 1826
Char:3
Code:0
When I do view Source on my browser: here's what I see for line 1826:
function navBarGetMousePosition(e){
|
by: Sneha Bele |
last post by:
Hi, I am beginner in Visual Basic. I am doing a simple ADODC project using MS Acces. In that when I add new record to the database it gives me error 'Object Required'.
I tried the same code onj...
|
by: ibrahim Nohbala |
last post by:
Hello.
I've written a small program in MS Access 2007 that calculates how many goods left in stock in 3 different units. For example, I have a product that has 3 units of measurement: gr,kg and...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |