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
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
Code:
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")
[COLOR=#ff0000]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[/COLOR]
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")
[B][COLOR=#ffa500]rsCon.Open szConnect[/COLOR][/B]
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
