ADODB.Connection : Sourcefile + if condition = error

[vadius]

Hi all,

I am trying to use ADODB connection stuff to read and copy data from a closed book.
The code will look for an excel file, read and copy the data into the sheet "PnL_T-1". Generally the macro will read the file dated as of yesterday (Date1), but sometimes I will need the macro to read a file dated as of another previous day (Date1_manual). So I want to add the condition IF (=> if there is a date entered manually, then the macro reads the file dated as of the manually entered date, and not the file as of yesterday). Otherwise, it reads the file as of yesterday.

In red I added the IF condition : My concern is I got a run time error message (in orange)

=> run-time error @-2147467259 (80004005) Cannot update. Database or object is read-only.

When I don't use the IF condition, the macro retrieves the data with no problem.

Has anyone an idea ? I think the confusion might come from the use of the same SourceFile name..

Thanks

Sub Copy_PnL_PC() Dim Year, Month, Date1, szConnect, Date1_manual, Year_manual, Month_Manual As String Dim SourceFile As Variant Dim SourceSheet, SourceRange, szSQL, calc_dt, mydate, mydate2 As String Dim rsCon, rsData As Object Dim Table As Variant Dim a As Variant Dim i, j As Integer Date1 = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P2").Value, "yyyymmdd") Year = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P2").Value, "yyyy") Month = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P2").Value, "mm") Date1_manual = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value, "yyyymmdd") Year_manual = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value, "yyyy") Month_Manual = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value, "mm") calc_dt = Sheets("PnL_T-1").Range("P2").Offset(j, 0).Value 'Manual entry date : if no pnl the previous day, want to retrieve a specific date. Enter the date in Q4 'Retrieve the data from the file dated as of date in cell Q4 mydate = ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value mydate2 = Format(mydate, "dd/mm/yyyy") If mydate <> 0 Then SourceFile = "P:\Lonib\Derivatives\Delta One Derivatives\Index Arbitrage - Swaps\P&L\" & Year_manual & "\Index_Arbitrage_London_FvA" & "_" & Format(mydate2, "yyyymmdd") & ".xls""" Else SourceFile = "P:\Lonib\Derivatives\Delta One Derivatives\Index Arbitrage - Swaps\P&L\" & Year & "\" & Year & " " & Month & "\Index_Arbitrage_London_FvA" & "_" & _ Format(calc_dt, "yyyymmdd") & ".xls" End If SourceSheet = "Summary" SourceRange = "A1:O33" If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No;"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=No;"";" End If szSQL = "SELECT * FROM [" & SourceSheet & "$" & SourceRange & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 Table = rsData.GetRows() For i = 1 To 32 For j = 0 To 13 ThisWorkbook.Worksheets("PnL_T-1").Range("A1").Offset(i, j) = Table(j, i) Next Next End Sub

[vadius]

Sub Copy_PnL_PC() Dim Year, Month, Date1, szConnect, Date1_manual, Year_manual, Month_Manual As String Dim SourceFile As Variant Dim SourceSheet, SourceRange, szSQL, calc_dt, mydate, mydate2 As String Dim rsCon, rsData As Object Dim Table As Variant Dim a As Variant Dim i, j As Integer Date1 = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P2").Value, "yyyymmdd") Year = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P2").Value, "yyyy") Month = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P2").Value, "mm") Date1_manual = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value, "yyyymmdd") Year_manual = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value, "yyyy") Month_Manual = Format(ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value, "mm") calc_dt = Sheets("PnL_T-1").Range("P2").Offset(j, 0).Value 'Manual entry date : if no pnl the previous day, want to retrieve a specific date. Enter the date in Q4 'Retrieve the data from the file dated as of date in cell Q4 mydate = ThisWorkbook.Worksheets("PnL_T-1").Range("P4").Value mydate2 = Format(mydate, "dd/mm/yyyy") If mydate <> 0 Then SourceFile = "P:\Lonib\Derivatives\Delta One Derivatives\Index Arbitrage - Swaps\P&L\" & Year_manual & "\Index_Arbitrage_London_FvA" & "_" & Format(mydate2, "yyyymmdd") & ".xls""" Else SourceFile = "P:\Lonib\Derivatives\Delta One Derivatives\Index Arbitrage - Swaps\P&L\" & Year & "\" & Year & " " & Month & "\Index_Arbitrage_London_FvA" & "_" & _ Format(calc_dt, "yyyymmdd") & ".xls" End If SourceSheet = "Summary" SourceRange = "A1:O33" If Val(Application.Version) < 12 Then szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=No;"";" Else szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceFile & ";" & _ "Extended Properties=""Excel 12.0;HDR=No;"";" End If szSQL = "SELECT * FROM [" & SourceSheet & "$" & SourceRange & "];" Set rsCon = CreateObject("ADODB.Connection") Set rsData = CreateObject("ADODB.Recordset") rsCon.Open szConnect rsData.Open szSQL, rsCon, 0, 1, 1 Table = rsData.GetRows() For i = 1 To 32 For j = 0 To 13 ThisWorkbook.Worksheets("PnL_T-1").Range("A1").Offset(i, j) = Table(j, i) Next Next End Sub

[Aflatoon]

& ".xls"""
should be simply
& ".xls"