VBA - Logging values to excel document, array needed?

[coretex99]

Good morning,

I am looking to log information stored in Access fields to a Excel sheet. My code is working as it stands however it overwrites every entry after the first one (as it is set to A2, B2 and C2)

I think I need an array to log each time the "export_click" is pressed to move the entry down to the next cell so I can get a full log of everytime it is pressed

Any help muchly appriciated, here is what I have, thank you

Private Sub Export_Click()
 
    Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
    .Visible = True
    .Workbooks.Open ("c:\temp\test.xlsx")
    .Sheets("Sheet1").Select
    .Range("A2") = Me.txtProduct.Value
    .Range("B2") = Me.txtQuantity.Value
    .Range("C2") = Me.txtCountUp.Value
    .ActiveWorkbook.Close True
    
End With
Set xlApp = Nothing

 

[Yongle]

UNTESTED
try with the 2 lines below omitting the dot ( . ) at beginning of line and insert lines to print values to Immediate Window
see Immediate Window in VBA editor with {CTRL} g
- this will help us work out what is going on

ActiveWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
Debug.Print Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value
Debug.Print ActiveWorkbook.Name
ActiveWorkbook.Close True

 

[coretex99]

UNTESTED
try with the 2 lines below omitting the dot ( . ) at beginning of line and insert lines to print values to Immediate Window
see Immediate Window in VBA editor with {CTRL} g
- this will help us work out what is going on

ActiveWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
Debug.Print Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value
Debug.Print ActiveWorkbook.Name
ActiveWorkbook.Close True

So the output is this.
alpha2 3 4
test.xlsx
alpha2 3 4
test.xlsx

IT works on the first press of the button but not the second press, Error 91, Object variable not set on the debug.print activeworkbook.name.

I noticed however the lock is still on the document once I run it the first time, so I assume it cannot write to it and thats why it is erroring, in task manager there are instances for everytime I have pressed the button, once these go the lock goes, and it runs the first time, then errors again

 

[Yongle]

I do not know why the file is being locked and am unable to test anything until next week
- but that is the cause of your latest problem
I suggest you start new thread because this is a different issue to what you wanted in post#1
Include the latest code and perhaps someone else can tell you how to prevent the file being locked

 

[coretex99]

Okay man, thanks for your help, hopefully will get there