Max Date from Another Workbook

[reberryjr]

I'm trying to pull the max date from column B of a source workbook and put it in column D of my main workbook. I've made the following attempts based on what I can find online, but none of them have worked. I cannot figure out what the issue is.

'Populates the Source Publicated/Last Updated column.
With mD.Range("D" & mDLR + 1 & ":D" & mNLR)
    '.Value = "=MAX('[Sub.xlsx]Sheet 1'!C[-2]:C[-2])"
    '.Value = Application.WorksheetFunction.Max(sD.Columns("B"))
    '.Value = Application.WorksheetFunction.Max(sD.Range("B2:B" & sDLR))
    MaxDt = Application.WorksheetFunction.Max(sD.Range("B2:B" & sDLR))
    .Value = MaxDt
    '.Value = .Value
End With

 

[Alex Blakenburg]

What does "not work" actually mean ? What happened ?
If you get an error message what is it and on what line ?

For that code to work the Source workbook has to open, is that the case ?
If the workbook is open, the code you have as being the current code should work.

Also you are only showing a small snipet of your code. Did you fully specify the workbook & worksheet when you retrieved the value for sDLR.
(and what about mDLR and nNLR)

Do you know how to use the immediate window to check the value of sDLR ?
Maybe add the line Debug.Print sDLR before your MaxDt line.

 

[reberryjr]

What does "not work" actually mean ? What happened ?
If you get an error message what is it and on what line ?

For that code to work the Source workbook has to open, is that the case ?
If the workbook is open, the code you have as being the current code should work.

Also you are only showing a small snipet of your code. Did you fully specify the workbook & worksheet when you retrieved the value for sDLR.
(and what about mDLR and nNLR)

Do you know how to use the immediate window to check the value of sDLR ?
Maybe add the line Debug.Print sDLR before your MaxDt line.

@Alex Blakenburg yes, everything is qualified correctly as the code pulls data from the source workbook and puts it in the destination workbook as desired. When I say the snippet is working, I mean that I'm only getting zeroes in the destination workbook. Well, adding the cDate, I now get a value of 12:00:00. The immediate window is returning a value of 0 as well. Here's the code in its entirety.

Option Explicit
Sub ImportSubData()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook, s As Workbook
Dim mD As Worksheet, sD As Worksheet
Dim MaxDt As Date
Dim fP As String, fN As String, fE As String
Dim mDLR As Long, mNLR As Long, sDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row

'Opens and sets the source file.
fP = "C:\Users\K085613\Desktop\Import Files\"
fN = "Sub"
fN = Dir(fP & fN & "*.xlsx")

Set s = Workbooks.Open(fP & fN)
Set sD = s.Sheets("Sheet 1")

sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row

sD.Activate

'Removes filters from the working data if any exist.
If sD.AutoFilterMode Then sD.AutoFilterMode = False

'Unhides any columns and rows that may be hidden on the working data.
With sD.UsedRange
    .Columns.EntireColumn.Hidden = False
    .Rows.EntireRow.Hidden = False
End With

'Populates the Loan Number column.
With sD.Range("C2:C" & sDLR).Copy
    mD.Range("F" & mDLR + 1).PasteSpecial xlPasteValues
End With

'Populates the Decision Date column.
With sD.Range("B2:B" & sDLR).Copy
    mD.Range("G" & mDLR + 1).PasteSpecial xlPasteValues
End With

'Populates the Decision column.
'Not applicable for this data source.

With sD.Range("B2:B" & sDLR).Copy
    mD.Range("I" & mDLR + 1).PasteSpecial xlPasteValues
End With

With sD.Range("F2:F" & sDLR).Copy
    mD.Range("L" & mDLR + 1).PasteSpecial xlPasteValues
End With

With sD.Range("H2:H" & sDLR).Copy
    mD.Range("M" & mDLR + 1).PasteSpecial xlPasteValues
End With

With sD.Range("I2:I" & sDLR).Copy
    mD.Range("P" & mDLR + 1).PasteSpecial xlPasteValues
End With

With sD.Range("G2:G" & sDLR).Copy
    mD.Range("Q" & mDLR + 1).PasteSpecial xlPasteValues
End With

mNLR = mD.Range("F" & Rows.Count).End(xlUp).Row

'Populates the LOB column.
mD.Range("A" & mDLR + 1 & ":A" & mNLR).Value = "Sub"

'Populates the Data Source column.
mD.Range("B" & mDLR + 1 & ":B" & mNLR).Value = "Sub-Data"

'Populates the Review Type column.
mD.Range("C" & mDLR + 1 & ":C" & mNLR).Value = "LOB"

'sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row

'Populates the Source Publicated/Last Updated column.
With mD.Range("D" & mDLR + 1 & ":D" & mNLR)
    '.Value = "=MAX('[Sub-HEPMO.xlsx]Sheet 1'!C[-2]:C[-2])"
    '.Value = Application.WorksheetFunction.Max(sD.Columns("B"))
    '.Value = Application.WorksheetFunction.Max(sD.Range("B2:B" & sDLR))
    MaxDt = Application.WorksheetFunction.Max(sD.Range("B2:B" & sDLR))
    'MaxDt = Application.WorksheetFunction.Max(s.Sheets("Sheet 1").Range("B2:B" & Rows.Count).End(xlUp).Row)
    .Value = CDate(MaxDt)
    '.Value = .Value
    '.FormulaR1C1 = "=MAX(" & "'[" & s.Name & "]Sheet 1'!C[-2]:C[-2])"
    '.Value = Replace(Left(Right(s.Name, 13), 8), ".", "/")
    '.Value = .Value

End With

'Populates the Source Ingested column.
With mD.Range("E" & mDLR + 1 & ":E" & mNLR)
    .Value = "=TODAY()"
    .Value = .Value
End With

With mD.Range("J" & mDLR + 1 & ":J" & mNLR)
    .Value = "=IF(RC[-1]="""","""",RC[-1])"
    .NumberFormat = "MMM-YY"
    .Value = .Value
End With

With sD.Range("D2:D" & sDLR).Copy
    mD.Range("K" & mDLR + 1).PasteSpecial xlPasteValues
End With

s.Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Alex Blakenburg]

When I say the snippet is working, I mean that I'm only getting zeroes in the destination workbook. Well, adding the cDate, I now get a value of 12:00:00. The immediate window is returning a value of 0 as well. Here's the code in its entirety.

CDate of 0 is 0 which is 12:00:00 when placed in a cell.
Please test your max function as a spreadsheet formula, if it doesn't work in as a normal formula in the spreadsheet it is certainly is not going to work in VBA.
If you change the format of the date column B does it actually change. If not then excel is reading that column as being text values which would account for Max returning 0.

If that part is ok then what I wanted to see in the immediate window is the value of sDLR.

If the above don't get you any where please show me what column B looks like.

 

[reberryjr]

CDate of 0 is 0 which is 12:00:00 when placed in a cell.
Please test your max function as a spreadsheet formula, if it doesn't work in as a normal formula in the spreadsheet it is certainly is not going to work in VBA.
If you change the format of the date column B does it actually change. If not then excel is reading that column as being text values which would account for Max returning 0.

If that part is ok then what I wanted to see in the immediate window is the value of sDLR.

If the above don't get you any where please show me what column B looks like.

The formula doesn't work as a spreadsheet formula either. The source column is formatted as date, so I'm not sure why it would read as text. What's weird is when I click into one of the cells in the source column, the value changes from 07/10/2023 to 07/10/23 and the destination workbook reflects 07/10/23. Here's the source column (I can't use B2B on my work laptop).

 

[Alex Blakenburg]

The formula doesn't work as a spreadsheet formula either. The source column is formatted as date, so I'm not sure why it would read as text. What's weird is when I click into one of the cells in the source column, the value changes from 07/10/2023 to 07/10/23 and the destination workbook reflects 07/10/23.

I don't know how you are populating the spreadsheet but changing to the format to Date after it has been populated as Text does not convert the data to being a date.
In fact changing the date / number format and having it have no effect is an easy way to prove Excel is seeing it as being text.

Try adding the code in blue in the position indicated, the black being your existing lines (actually anywhere before you try and calculate the Max Date will do)
It converts column B to being Date (with the source format being mm/dd/yyyy)

'Populates the Source Publicated/Last Updated column.
sD.Range("B1:B" & sDLR).TextToColumns Destination:=sD.Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 3), TrailingMinusNumbers:=True

With mD.Range("D" & mDLR + 1 & ":D" & mNLR)

 

[reberryjr]

I don't know how you are populating the spreadsheet but changing to the format to Date after it has been populated as Text does not convert the data to being a date.
In fact changing the date / number format and having it have no effect is an easy way to prove Excel is seeing it as being text.

Try adding the code in blue in the position indicated, the black being your existing lines (actually anywhere before you try and calculate the Max Date will do)
It converts column B to being Date (with the source format being mm/dd/yyyy)

'Populates the Source Publicated/Last Updated column.
sD.Range("B1:B" & sDLR).TextToColumns Destination:=sD.Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 3), TrailingMinusNumbers:=True

With mD.Range("D" & mDLR + 1 & ":D" & mNLR)

@Alex Blakenburg so that works, but I don't understand why this is an issue. The source data is formatted as date, so shouldn't it come over as date?

 

[Alex Blakenburg]

I don't understand why this is an issue. The source data is formatted as date, so shouldn't it come over as date?

You would need step me through you process.
The spreadsheet referred to as the "source" is clearly not the originating source. Although the dates appear to be formatted they are in that workbook as "text" (chaning the format will not change them because they are Text)
So how did the data get into that workbook ? Is the originating source a csv file ? How did you import it ?

 

[reberryjr]

You would need step me through you process.
The spreadsheet referred to as the "source" is clearly not the originating source. Although the dates appear to be formatted they are in that workbook as "text" (chaning the format will not change them because they are Text)
So how did the data get into that workbook ? Is the originating source a csv file ? How did you import it ?

While the data I'm using comes from a different source/repository, it is the source data that is available to me. I simply save the file to a folder, open it with the code and bring data into my destination workbook.