Access 2010 VBA To Get Excel EndRow To Create Simple Progress Bar

[PWD]

Good afternoon. I'm trying to put a quick Progress Bar into Access and found this on "pyeung.com"

Dim Counter As Integer

SysCmd acSysCmdInitMeter, "Updating: ", 1000
For Counter = 1 To 1000
    SysCmd acSysCmdUpdateMeter, Counter
Next Counter
SysCmd acSysCmdRemoveMeter

I have this code to open an Excel spreadsheet

Set xlw = xlx.Workbooks.Open("G:\DP\Pamela\Reuters\Ben\TRCS_AMERICAS_Template.xls", , True) ' opens in read-only mode

Set xls = xlw.Worksheets("TRCS_america")

Set xlc = xls.Range("A2") ' this is the first cell that contains data

And this code to write each cell from Excel to Access (because it's the ONLY WAY I've found to import more than 255 characters!!)

' write data to the recordset

Do While xlc.Value <> ""
      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update
      Set xlc = xlc.Offset(1, 0)
Loop

So I thought that if I obtained the row number of the final row with data in the worksheet, I could end up with something like

SysCmd acSysCmdInitMeter, "Updating: ", xlEndRow

x = 1

Do While xlc.Value <> ""
 
  SysCmd acSysCmdUpdateMeter, x

      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update

      Set xlc = xlc.Offset(1, 0)

x =  x +1

Loop

SysCmd acSysCmdRemoveMeter

Unfortunately

xlEndRow = xls.Range("A65555").End(xlUp).Row

only shows 0.

Is it possible to do what I "want" or is there an alternative simple way to create such a Progress Bar?

Many thanks,

Des.

 

[SkipVought]

hi,

with xls
 xlEndRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Good morning Skip. Dang, it still only shows "0" when I hover over "xlEndRow".

I thought of this on the train home Friday night - & it appears to work.

Do While xlc.Value <> ""
Set xlc = xlc.Offset(1, 0)
Loop

xlEndRow = xlc.row - 1

I might just 'progress' now into putting a rectangle within a rectangle on my Form to make things a bit more "in your face" as far as the user is concerned.

Many thanks anyway.

Des.

 

[PWD]

This is what I have now:-

Set xlc = xls.Range("A2") ' this is the first cell that contains data

Do While xlc.Value <> ""
Set xlc = xlc.Offset(1, 0)
Loop
xlEndRow = xlc.row - 1

'Reset xlc
Set xlc = xls.Range("A2") ' this is the first cell that contains data

Set dbs = CurrentDb()
' The name of the table or query that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("TRCS_Americas", dbOpenDynaset, dbAppendOnly)

'Show Progress Bar
With Form_TRCS_output
.boxProgressBar.Visible = True
.rectProgressBar.Visible = True
End With

intCurrentProgress = 1

' write data to the recordset
Do While xlc.Value <> ""
      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update

        'Progress Bar
        Form_TRCS_output.rectProgressBar.Width = (intTotalWidth / xlEndRow) * intCurrentProgress
        Form_TRCS_output.Repaint
        intCurrentProgress = intCurrentProgress + 1
      
      Set xlc = xlc.Offset(1, 0)
Loop

'Hide Progress Bar
With Form_TRCS_output
.boxProgressBar.Visible = False
.rectProgressBar.Visible = False
End With

Des.

 

[PWD]

Whoops. Guess who forgot to set any value for this?

 intTotalWidth

"Git-R-Done!!"

Many thanks,
D€$

 

[SkipVought]

don't need to loop to find last row...

    Dim xlc As Range, r As Range
    
    Set xlc = xls.Range("A2") ' this is the first cell that contains data
    Set xlc = Range(xlc, xlc.End(xlDown)) ' this is the column A range of data

    
    xlEndRow = xlc.Row + xlc.Rows.Count - 1
    
    Set dbs = CurrentDb()
    ' The name of the table or query that is to receive the data from the worksheet
    Set rst = dbs.OpenRecordset("TRCS_Americas", dbOpenDynaset, dbAppendOnly)
    
    'Show Progress Bar
    With Form_TRCS_output
        .boxProgressBar.Visible = True
        .rectProgressBar.Visible = True
    End With
    
    intCurrentProgress = 1
    
    ' write data to the recordset
    For Each r In xlc
        rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                rst.Fields(lngColumn).Value = r.Offset(0, lngColumn).Value
            Next lngColumn
        rst.Update

        'Progress Bar
        Form_TRCS_output.rectProgressBar.Width = (intTotalWidth / xlEndRow) * intCurrentProgress
        Form_TRCS_output.Repaint
        intCurrentProgress = intCurrentProgress + 1
    Next
    
    'Hide Progress Bar
    With Form_TRCS_output
        .boxProgressBar.Visible = False
        .rectProgressBar.Visible = False
    End With

then this will

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}