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

 

[Sunjinsak]

Hi, and welcome to the forum!

Untested, but try something like this...

    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")

    With xlApp

        .Visible = True
        .Workbooks.Open ("c:\temp\test.xlsx")
        .Sheets("Sheet1").Select
     
        With ActiveSheet
            .Range("A2").Select
            Do
            If Not IsEmpty(ActiveCell) Then
                'If A2 already has a value in it move to the next row down...
                ActiveCell.Offset(1, 0).Select
            End If
            'Keep moving down until we find the first empty row...
            Loop Until IsEmpty(ActiveCell)
            'Put the data in Cells Ax, Bx and Cx...
            ActiveCell.Value = Me.txtProduct.Value
            ActiveCell.Offset(0, 1).Value = Me.txtQuantity.Value
            ActiveCell.Offset(0, 2).Value = Me.txtCountUp.Value
        End With

       .ActiveWorkbook.Close True

    End With

    Set xlApp = Nothing

 

[Yongle]

UNTESTED

Try this

     .Range("A" & rows.count).end(xlup).resize(,3) =array( Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)

 

[coretex99]

UNTESTED

Try this

     .Range("A" & rows.count).end(xlup).resize(,3) =array( Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)

Hi Yongle, thank you for this, this does work and fills in a2 b2 and c2, however when I press the button again it just replaces it and puts it in the same cells, not a3 b3 and c3, any ideas? Thanks!!

 

[Yongle]

oops

.Range("A" & rows.count).end(xlup).Offset(1).resize(,3) =array( Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)

 

[coretex99]

Hi, and welcome to the forum!

Untested, but try something like this...

    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")

    With xlApp

        .Visible = True
        .Workbooks.Open ("c:\temp\test.xlsx")
        .Sheets("Sheet1").Select
    
        With ActiveSheet
            .Range("A2").Select
            Do
            If Not IsEmpty(ActiveCell) Then
                'If A2 already has a value in it move to the next row down...
                ActiveCell.Offset(1, 0).Select
            End If
            'Keep moving down until we find the first empty row...
            Loop Until IsEmpty(ActiveCell)
            'Put the data in Cells Ax, Bx and Cx...
            ActiveCell.Value = Me.txtProduct.Value
            ActiveCell.Offset(0, 1).Value = Me.txtQuantity.Value
            ActiveCell.Offset(0, 2).Value = Me.txtCountUp.Value
        End With

       .ActiveWorkbook.Close True

    End With

    Set xlApp = Nothing

Thanks for this, however I get an error 91 object variable with block not set when buttom pressed, on the .Range("A2").Select line

 

[Yongle]

Please post the sub that are now testing (in full) so that I can see what is going on
Thanks

 

[coretex99]

Please post the sub that are now testing (in full) so that I can see what is going on
Thanks

Thanks for the help however we now have another error, wish I was better at this

Run-time error '1004' Method 'Rows' of object '_Global' Failed

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("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
    .ActiveWorkbook.Close True
    
End With
Set xlApp = Nothing

    
End Sub

 

[Yongle]

UNTESTED
Try this which avoids selecting the sheet etc

Private Sub Export_Click()
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Open ("c:\temp\test.xlsx")
        .ActiveWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
        .ActiveWorkbook.Close True
    End With
    Set xlApp = Nothing
End Sub

 

[coretex99]

UNTESTED
Try this which avoids selecting the sheet etc

Private Sub Export_Click()
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Open ("c:\temp\test.xlsx")
        .ActiveWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
        .ActiveWorkbook.Close True
    End With
    Set xlApp = Nothing
End Sub

Well this time nothing errors, but nothing gets imported into the file :/ I have checked and made sure there are no locks on the file.
With Offset(1) would this move it down each time the button is pressed, or would it always just be an offset from A?

Thanks again for your help man