Retrieve data from last row of specific Columns

[DMT Dave]

Hi Guy's, I am fairly close with this one and require you great guy's to correct me



I am trying open an excel file on sheet 3 and retrieve data from the last row on Columns A, B and F

all looks ok to me apart from:

I can't seem to open the file on sheet 3

therefore strings strColA, strColB and strColF are not returning the correct data

Have i got this correct apart from not opening on Sheet 3 ?

    Dim xlApp As Object    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim strPath As String, strFile As String, strColA As String, strColB As String, strColF As String
    Dim iSheet As Integer, i As Integer, intLR As Integer, iRow As Integer
    Dim varResult As Variant
    
    iSheet = "3"
       
    strPath = "T:\DMT Ltd\Fuel Price Sheets\"
    strFile = "Fuel Prices.xlsx"


    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open(strPath & strFile)
    Set xlWsh = xlWbk.Worksheets(iSheet)
    xlApp.Visible = True
    
    With xlWsh
    
    intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To intLR
            
            strColF = xlWsh.Range("F" & i).Value
            strColA = xlWsh.Range("A" & i).Value
            strColB = xlWsh.Range("B" & i).Value
        
        Exit For
        
        Next i
        
    End With
    
    Debug.Print strColA
    Debug.Print strColB
    Debug.Print strColF
    
        MsgBox ("the data on last rows of Column A, B and F is:" & vbCrLf & vbCrLf & _
            strColA & vbCrLf & _
            strColB & vbCrLf & _
            strColF)
    
    'Application.FollowHyperlink strPath & strFile
    
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

[June7]

It is not necessary to set focus to specific sheet in order to pull its data. I tested your code and it works. However, it is not pulling data from last row, it is first row. Why do you have Exit For in the loop? That causes exit in the first iteration. If you want only the last row data, there is no need to loop.

[DMT Dave]

Ahh June7, of course, i have just read the MsgBox and it is returning data from Row A

So do i need to remove any for or looping statements ?

[June7]

You can remove the Exit For line. Again, if you only want last row, don't need loop - remove For and Next lines. Just reference the last row with intLR instead of i variable.

[DMT Dave]

June7, perfect thank you so much working correctly

    Dim xlApp As Object    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim strPath As String, strFile As String, strColA As String, strColB As String, strColF As String
    Dim iSheet As Integer, i As Integer, intLR As Integer, iRow As Integer
    Dim varResult As Variant
    
    iSheet = "3"
       
    strPath = "T:\DMT Ltd\Fuel Price Sheets\"
    strFile = "Fuel Prices.xlsx"


    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open(strPath & strFile)
    Set xlWsh = xlWbk.Worksheets(iSheet)
    xlApp.Visible = False
    
    With xlWsh
    
    intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        strColF = xlWsh.Range("F" & intLR).Value
        strColA = xlWsh.Range("A" & intLR).Value
        strColB = xlWsh.Range("B" & intLR).Value
        
    End With
    
    Debug.Print strColA
    Debug.Print strColB
    Debug.Print strColF
    
        MsgBox ("the data on last rows of Column A, B and F is:" & vbCrLf & vbCrLf & _
            strColA & vbCrLf & _
            strColB & vbCrLf & _
            strColF)
    
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

[June7]

Since you are using With xlWsh, there is no need to repeat xlWsh within that code block.

[DMT Dave]

I am now trying to workout how i can add to this to retrieve the last 12 rows used, is that possible ?

[DMT Dave]

would i use something like:

        For i = -12 To intLR        strColF = xlWsh.Range("F" & i).Value
        strColA = xlWsh.Range("A" & i).Value
        strColB = xlWsh.Range("B" & i).Value
        
        Next i

Or use to step - 12 ?

[DMT Dave]

Hi June7, now cleared xlWsh from within code block

I i wanted to get data for last 12 rows, i have tried adding a loop back in but not returning data

    With xlWsh    
    intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    For intLR = 1 To -12
        
        strColF = .Range("F" & intLR).Value
        strColA = .Range("A" & intLR).Value
        strColB = .Range("B" & intLR).Value
    
    Next intLR
    
    End With

[Minty]

A number of things, although Excel probably doesn't care
Sheet(x)
would expect x to be a number not a string.

Your last bit of code is constantly rewriting the variables with the new values, so
strColF (and the others) will, in very quick succession, have the value of the last cell, the one above it, the one above that etc. etc.

You're not doing anything with the value each time other than overwriting it.

[June7]

What do you want do with retrieved data? As Minty noted, code only outputs the last data read within the loop. If you want to do something (Debug.Print, save to somewhere else, etc) with each row of data, that needs to be within the loop.

[Gicu]

Dave,
your loop should be something like this:

 With xlWsh    
    intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
    Dim i as integer
    For i = 0 to 11
        
        strColF = .Range("F" & intLR-i).Value
        strColA = .Range("A" & intLR-i).Value
        strColB = .Range("B" & intLR-i).Value
    
    Next i    
End With

As Minty said, you are not doing anything with the strCol variables so you might want to declare them as string arrays and load them in the For loop:

Dim strColF() as string
Dim strColA() as string
Dim strColB() as string


 With xlWsh    
    intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
    Dim i as integer
    For i = 0 to 11
        
        strColF(i) = .Range("F" & intLR-i).Value
        strColA(i) = .Range("A" & intLR-i).Value
        strColB(i) = .Range("B" & intLR-i).Value
    
    Next i    
End With

Cheers,

[DMT Dave]

Thanks Vlad, OK so declare the arrays and add the For within the arrays for each strCOL

I can see what you have done

thank you

[DMT Dave]

Hi Guys, im back to this and trying for life of me to work out how to add differences between records and edit the set and add to Difference field

I know im doing the wrong thing by using DAvg as all field will populate with an average in all records

How do I add something in that will add difference between each record ?

This should be something i can do easily but tried a few different things without success!!!

This is on my "Add Calculations Button"

Dim rs As dao.Recordset    Dim curRate As Currency, curDiff As Currency, curFuelPrice As Currency, curAvg As Currency, curMax As Currency
    Dim i As Integer
    
    
        Set rs = CurrentDb.OpenRecordset("Select * From tblFuelPrices")
            
            With rs
                
            Do While Not rs.EOF
            
                curAvg = DAvg("FuelPrice", "tblFuelPrices")
                    
                rs.Edit
                !Difference = curAvg
                .Update
                rs.MoveNext
            
            Loop
            
            End With


Set rs = Nothing

[Minty]

Step through the process, or create the calculation in Excel one at a time.
Then apply the same logic to your access calculations.

Curr average is fixed so move it out out the loop.

The difference is going to be rs.FuelPrice - CurrAvg