lookforsmt
Registered User.
- Local time
- Today, 16:39
- Joined
- Dec 26, 2011
- Messages
- 672
HI! all, Wish you all a Merry X'mas and Happy New Year 2018.
i have below module which when called from form button,"Call Export_Data", exports data in separate excels.
It looks for the table or query name listed in the table, "MyExport" and then exports excel in the mentioned location.
I have a challenge, the table, "MyExport" has 3 queries/Tables mentioned but one of the table is missing in the db so i get an error.
How can i ignore the error and move to the next table or query listed in the table.
I am thinking if i add a field name Active and Data Type (Yes/No) then only the ones which are checked should be considered for export and if not checked should be ignored.
Below is the code:
Thanks
i have below module which when called from form button,"Call Export_Data", exports data in separate excels.
It looks for the table or query name listed in the table, "MyExport" and then exports excel in the mentioned location.
I have a challenge, the table, "MyExport" has 3 queries/Tables mentioned but one of the table is missing in the db so i get an error.
How can i ignore the error and move to the next table or query listed in the table.
I am thinking if i add a field name Active and Data Type (Yes/No) then only the ones which are checked should be considered for export and if not checked should be ignored.
Below is the code:
Code:
Option Compare Database
Option Explicit
'------------------------------------------------------------
' Export_Data
'
'------------------------------------------------------------
Sub Export_Data()
On Error GoTo Export_Data_Err
'add object and scalar variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim TheTable As String
Dim TheFile As String
'open object variables
Set db = CurrentDb
Set rs = db.OpenRecordset("MyExport")
'loop through recordset
Do While Not rs.EOF
'set scalar variables
TheTable = rs!Export_Table
TheFile = rs!Export_Filename
'export the table
DoCmd.TransferSpreadsheet acExport, 10, TheTable, _
TheFile, True, ""
'move to the next record
rs.MoveNext
Loop
MsgBox "Created xlsx files"
Export_Data_Exit:
Exit Sub
Export_Data_Err:
MsgBox Error$
Resume Export_Data_Exit
End Sub
Thanks