Hello all,
I am trying to automate Excel via vba(access). If I try to write values in
the second sheet (InkNieuweleden)of the workbook the program puts these
values always in the first sheet (InkBijbetalers). Can anybody tell me what
I'm doing wrong here?
Here follows a snippet of the code:
Private Sub Command44_Click()
DoCmd.Hourglass True
Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet
If fIsAppRunning("Excel") Then
Set ObjXL = GetObject(, "Excel.Application")
'boolXL = False
Else
Set ObjXL = CreateObject("Excel.Application")
'boolXL = True
End If
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\Boekhouding_WorkCopy. xls", ,
False)
Set ObjXLSheet = ObjXLBook.Worksheets("InkNieuweleden")
ObjXL.Cells(1, 1).Value = "Bestand aangemaakt op: " & Date
Aantalkols = 193
Rij = 5
Kol = 1
teller = 1
Do While Rij < 25
teller = 1
Do While teller < 6
Do While Kol < Aantalkols + 1
ObjXL.Cells(Rij, Kol).Value = "this is an emptystring - will
change that later with.select"
Kol = Kol + 6
Loop
Aantalkols = Aantalkols + 1
teller = teller + 1
Kol = teller
Loop
Rij = Rij + 1
Kol = 1
Aantalkols = 193
Loop
Dim dbs1 As DAO.Database
Dim rst1 As DAO.Recordset
Dim strSQL1 As String
strSQL1 = "SELECT * FROM Leden where [Betaald] = 10 and [Oudgediende] =
False order by [Achternaam]"
Set dbs1 = CurrentDb
Set rst1 = dbs1.OpenRecordset(strSQL1)
If rst1.RecordCount <> 0 Then
rst1.MoveFirst
Rij = 5
Kol = 1
Aantalkols = 193
Do While Not rst1.EOF And Kol < Aantalkols + 1
ObjXL.Cells(Rij, Kol).Value = rst!DatumBetaling
ObjXL.Cells(Rij, Kol + 1).Value = rst!Betaald - Eerstbetaald
ObjXL.Cells(Rij, Kol + 2).Value = rst!ID
ObjXL.Cells(Rij, Kol + 3).Value = rst!Achternaam
ObjXL.Cells(Rij, Kol + 4).Value = rst!Voornaam
Rij = Rij + 1
If Rij = 25 Then
Rij = 5
Kol = Kol + 6
End If
rst1.MoveNext
Loop
If Not rst1 Is Nothing Then
rst1.Close
Set rst1 = Nothing
End If
If Not dbs1 Is Nothing Then
Set dbs1 = Nothing
End If
End If
ObjXLBook.Save
DoCmd.Hourglass False
ObjXL.Visible = True
End Sub
