Hi all,
I am trying to have a process where scheduled reports i receive to email inbox will be downloaded to file and attached to the bottom of existing table in a file.
I managed to create the code below using some VBA knowledge and google.
Error code i get is run time error 6 - overflow. Google advises that this is due to file size (One csv file has around 3 mb and between 25k and 30k rows.)
Error via debugger on bolded
Sub CopyAttachmentToExcel(olitem As Outlook.MailItem)
'olitem is needed in sub name as it is a variable
Dim xlApp As Object
Dim xlWB As Object
Dim xlTempWB As Object
Dim xlSheet As Object
Dim xlTempSheet As Object
Dim lngTempLast As Integer
Dim lngLast As Integer
Dim strFname As String
Dim strTempPath As String
Dim bXLStarted As Boolean
'the path and name of the local workbook
Const strPath As String = "C:\Users\testname\Desktop\Agent State Raw\State reports\February 19.xlsx"
strTempPath = Left(strPath, InStrRev(strPath, "")) 'The path of the temporary file
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
bXLStarted = True
End If
xlApp.Visible = True
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("feb19") 'The sheet in the local workbook
'Process the message attachment
With olitem.Attachments.Item(1)
If Right(.DisplayName, 3) = "csv" Then
lngLast = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
strFname = strTempPath & .DisplayName
.SaveAsFile strFname
Set xlTempWB = xlApp.Workbooks.Open(strFname, editable:=True)
Set xlTempSheet = xlTempWB.Sheets("Agent State Details Report All ") 'sheet data is copied from
lngTempLast = xlTempSheet.Range("B" & xlTempSheet.Rows.Count).End(-4162).Row
'in code below H means last column it will copy to (range limit)
xlSheet.Range("A" & lngLast, "M" & lngLast + lngTempLast - 1).Value = xlTempSheet.Range("A2", "M" & lngTempLast).Value
xlWB.Save
End If
End With
xlWB.Close SaveChanges:=True
xlTempWB.Close SaveChanges:=False
If bXLStarted Then
xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
Set xlTempWB = Nothing
Set xlTempSheet = Nothing
Set olitem = Nothing
End Sub
Any chance for some clarification?
Filarap
I am trying to have a process where scheduled reports i receive to email inbox will be downloaded to file and attached to the bottom of existing table in a file.
I managed to create the code below using some VBA knowledge and google.
Error code i get is run time error 6 - overflow. Google advises that this is due to file size (One csv file has around 3 mb and between 25k and 30k rows.)
Error via debugger on bolded
Sub CopyAttachmentToExcel(olitem As Outlook.MailItem)
'olitem is needed in sub name as it is a variable
Dim xlApp As Object
Dim xlWB As Object
Dim xlTempWB As Object
Dim xlSheet As Object
Dim xlTempSheet As Object
Dim lngTempLast As Integer
Dim lngLast As Integer
Dim strFname As String
Dim strTempPath As String
Dim bXLStarted As Boolean
'the path and name of the local workbook
Const strPath As String = "C:\Users\testname\Desktop\Agent State Raw\State reports\February 19.xlsx"
strTempPath = Left(strPath, InStrRev(strPath, "")) 'The path of the temporary file
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
bXLStarted = True
End If
xlApp.Visible = True
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("feb19") 'The sheet in the local workbook
'Process the message attachment
With olitem.Attachments.Item(1)
If Right(.DisplayName, 3) = "csv" Then
lngLast = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
strFname = strTempPath & .DisplayName
.SaveAsFile strFname
Set xlTempWB = xlApp.Workbooks.Open(strFname, editable:=True)
Set xlTempSheet = xlTempWB.Sheets("Agent State Details Report All ") 'sheet data is copied from
lngTempLast = xlTempSheet.Range("B" & xlTempSheet.Rows.Count).End(-4162).Row
'in code below H means last column it will copy to (range limit)
xlSheet.Range("A" & lngLast, "M" & lngLast + lngTempLast - 1).Value = xlTempSheet.Range("A2", "M" & lngTempLast).Value
xlWB.Save
End If
End With
xlWB.Close SaveChanges:=True
xlTempWB.Close SaveChanges:=False
If bXLStarted Then
xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
Set xlTempWB = Nothing
Set xlTempSheet = Nothing
Set olitem = Nothing
End Sub
Any chance for some clarification?
Filarap
