Microsoft Office Tutorials and References
In Depth Information
Transferring Data from E-mail Attachments to Excel
' * * * * *¶
Private Sub ExtractDataFile(objAttachment As Attachment)¶
'This procedure reads the source file¶
'Adds/Updates data in main data workbook¶
'Saves attachment into temporary folder¶
'FileSystemObject Objects declaration¶
Dim fso As Object¶
Dim strTempPath As String¶
Dim strFileName As String¶
'Create File System Object¶
'to retrieve temporary folder path¶
Set fso = CreateObject("Scripting.FileSystemObject")¶
' = 2 Temporary Folder¶
strTempPath = fso.GetSpecialFolder(2)¶
Set fso = Nothing¶
'Save attachment in temporary folder¶
'by using a unique filename in Temp folder¶
strFileName = strTempPath & "\" & _¶
Format(Now, "ddmmyyhhmm") & ".xls"¶
objAttachment.SaveAsFile strFileName¶
'Call Read/Write module¶
Call TransferData(strFileName)¶
End Sub¶
' * * * * *¶
Private Sub TransferData(strSourceFile As String)¶
'This module opens source and target workbooks¶
'and transfers requested data¶
'No need for an Excel Object Library reference¶
'since Late Binding is used¶
'Excel Application Object declarations¶
Dim xlsApp As Object 'Excel Application¶
Dim wrkSource As Object 'Source Workbook Object¶
Dim wrkTarget As Object 'Target Workbook Object¶
Dim shtSource As Object 'Source Worksheet Object¶
Dim shtTarget As Object 'Target Worksheet Object¶
Dim rngSource As Object 'Source Range Object¶
Dim rngTarget As Object 'Target Cell Object¶
Dim cll As Object 'Worksheet Cell object¶
On Error GoTo ErrorHandler¶
'Create new Excel Application¶
Set xlsApp = CreateObject("Excel.Application")¶
'Set source workbook¶
Set wrkSource = xlsApp.workbooks.Open(strSourceFile)¶
'Set source worksheet¶
'It is supposed to be only one worksheet¶
'So using index 1¶
Set shtSource = wrkSource.Worksheets(1)¶
'Set target workbook¶
Set wrkTarget = xlsApp.workbooks.Open(strTargetFile)¶
Search JabSto ::

Custom Search