I am building a database which relies on data generated by an automatic script. This script creates a new directory every
day, and each hour a new Excel file is stored as an attachment within a .msg file.
Currently, I have to open each
.msg file individually, open the attachment, and then collate the data from each of the 24 files into one spreadsheet. This
is then manually imported into my database.
The first file is called Unsuccessful SRC jobs Report.msg, successive
ones are named Unsuccessful SRC jobs Report (2).msg etc.
Currently, the code I have looks like this.
Option Compare Database
Public Function Import_SRC() As Boolean
Dim v_excel As New Excel.Application
Dim v_filename As String
'Open file browser to allow user to select an SRC message
v_filename = v_excel.GetOpenFilename
If v_filename = "False" Then
MsgBox "Cancelled, process abandoned"
'Strips unneccessary characters from filename
If InStr(1, v_filename, "(") > 0 Then
v_filename = Mid(v_filename, 1, InStr(1, v_filename, "(") - 2)
v_filename = Mid(v_filename, 1, InStr(1, v_filename, ".") - 1)
open_message (v_filename & ".msg")
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The purpose of this is to open a file browser so that the user can browse to the correct daily directory. On selecting
any of the 24 messages, I strip the last few digits to leave me with /Unsuccessful SRC jobs Report
Once the path
is stored in v_filename, I need code to open each message in turn, open the attachment contained within, and import the data
in the spreadsheet into a table called SRC. The first time this is done, it should create the table, successive iterations
should append the data.