Tracking of Excel transfer

[John liem]

I am transfering Excel files File_1, File_2, File_3 ... File_n into a table, these files have the same format but different data.
Fm_Transfer has a button to do the automatic transfer with following code:

Dim n As Integer
Dim cntBook As String
Dim vPath As String
Dim vFile As String
n = 1
cntBook = ""
vPath = "C:\Database\"
vFile = ""
For n = 1 To 360
cntBook = "File_" & n
vFile = vPath & cntBook
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_File", vFile, True
On Error GoTo X
Next n
How can I visualize in a this form (Fm_Transfer) which Excel file is Access tranferring?,
Like "Now transferring File_1, File_2, etc ....."
Thanks.

 

[KenHigg]

---------------------------
Dim n As Integer
Dim cntBook As String
Dim vPath As String
Dim vFile As String
Dim sMsg as string
sMsg = "Now transferring "

n = 1
cntBook = ""
vPath = "C:\Database\"
vFile = ""
For n = 1 To 360
cntBook = "File_" & n
vFile = vPath & cntBook
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_File", vFile, True
On Error GoTo X
sMsg = sMsg & "vfile, "
me!txtMessageBox = sMsg
Next n
me!txtMessageBox = ""
---------------------------


Where me!txtMessageBox is a text box on your form...

???
ken

 

[John liem]

Hi Ken,
It works fine, thanks.

Any ideas how the VBA would be if I would do it from Excel to Acces, meaning if I would create VBA in Excel and transfer the files to Access? Instead of importing the files, I export the files from Excel to Access. I just want to know for my knowledge. Thanks in advance!

 

[KenHigg]

Got me on that one - Might repost it in the Excel forum (?)


kh

 

[John liem]

How can I insert the contents of this "txtMessageBox" into a Table (Tbl_Text) for tracking?

 

[KenHigg]

Do an append query in the query builder and execute it in the same block of code?

kh


(fyi - Sounds like a good idea)

 

[John liem]

Hi Ken,
Works fine, thanks again!
John.