Export to Excel

[lookforsmt]

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:

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

 

[lookforsmt]

just attaching the image snapshot for clarity.

 

[isladogs]

Add code to check whether table exists
Place the following code in a standard module

Public Function CheckTableExists(strTable As String) As Boolean

'Checks if named table exists
     
On Error Resume Next

'If table exists, strName will be <> ""
   [B]Dim strName as String[/B]
    strName = CurrentDb.TableDefs(strTable).Name
    CheckTableExists = Not (strName = "")
    'Debug.Print strTable & ": " & CheckTableExists
    
    'tidy up
    strTable = "" 
    strName = "" 

End Function

Then add the code in red to your existing 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
    
  [COLOR="Red"] If CheckTableExists("TheTable") Then[/COLOR]
        'export the table
        DoCmd.TransferSpreadsheet acExport, 10, TheTable, _
            TheFile, True, ""
    [COLOR="red"]End If     [/COLOR]
    '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

 

[lookforsmt]

thanks ridderss
i have saved the module and add the code in red. Also, added field Active in the table "MyExport".

I a getting below error

Variable not defined

 

[isladogs]

Apologies
Add the missing line Dim strName As String
I've edited my previous post to include it

 

[lookforsmt]

No problem, but i added the variable before your post, thought there was still some code, as i am getting now a another error

Run-time error 3265
Item not found in this collection

 

[isladogs]

Error 3265 will be in the recordset code.
Try adding breakpoints to each line then step through the code to see what triggers the error
Also add a Debug.Print line before the If line

Debug.Print TheTable

This will help you identify which table(s) trigger the error
Alternatively remove the single quote at the start of the debug line in my function

 

[lookforsmt]

Sorry, i am not able to figure out the issue, may i request you if i can upload my db. If you can look at it.

thanks

 

[isladogs]

That's fine.
When you do so, provide info on how to trigger the error

 

[lookforsmt]

Thanks, i have uploaded the db

If none of the checkbox is ticked it should popup message to user "no table or query selected" and stop the code
or
if table has a file or query listed but not available in the db then pop msg to alert user.

May i request the below additionally if this is not much of trouble for you.

i) Can the file exported have the date inserted in the file name.
ii) i wanted table "ExportDetails" to records each and every export by date and time along the file name.

 

[isladogs]

Hmm....

Lots of issues:

1. Lots of redundant references

2. There's no point checking for tables as you are using queries.
So you need code to check if a query exists

3. For queries you should use a dynaset

rs = db.OpenRecordset("MyExport", dbOpenDynaset)

4. What does '10' mean in the next line?

DoCmd.TransferSpreadsheet acExport, 10, TheTable, TheFile, True

For xlsx files you should use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, TheTable, TheFile, True

5. Why are StartDate & EndDate parameters requested?

6. What is the point of the Active field?

7. At the end, you should add:

Set rs  = Nothing

8. Where an Excel file is created it is empty & has no workbooks
Did it work correctly for you?

And that's before looking at your extra requests
Suggest you try to fix the export so it works where you do have a query

Sorry, but I'm going to pass on this one as I don't have the time to work through all the issues

Hopefully someone else will take over and assist

 

[lookforsmt]

Thanks ridders, for looking into, i will try and work on the suggestions and hopefully someone can assist me.

i actually took this code from one of the forum. Just to answer your query

4) I have no idea what the 10 relates to
5) Start and End date to retrieve data for a particular period
6) Active field was only to export from the ones which are checked in the table.

Thanks again for your time.

Take care

 

[lookforsmt]

HI! all, Wishing you all a very Happy New Year ! 2018 !

i have the below code which saves the excel file after opening the FileDialog in the folder picked up from the table (TableList). But when i click on the export button opens the FileDialog but at this point if i dont want to save the file and cancel the export, it shows below runtime error

runtime error 1004
The file could not be accessed. Try one of the following
- Make sure the specified folder exists
- Make sure the folder that contains the file is not read-only
- Make sure the file name contains any of the following
characters: < > ? [ ] : or *

Below is my code. I want to put the error handler but not sure which line to put the code

Private Sub cmdExport_Click()

    Dim db As DAO.Database
    Dim rsDriver As DAO.Recordset
    Dim rsSrc As DAO.Recordset
    Dim xlApp As Excel.Application
    
    Dim wbDest As Excel.Workbook
    Dim wsDest As Excel.Worksheet
    Dim Fdia As FileDialog
    
    Dim ThisTable As String
    Dim NameSheet As String
    Dim FilToSave
    Dim i As Long
    Dim strSQL As String        '-- Create SQL update statement
    Dim errLoop As Error
    
    Me.Dirty = False  ' if you've changed any fields, you need to save the form or you'll still be working with the old value
    
    Set xlApp = CreateObject("Excel.Application")
    Set wbDest = xlApp.Workbooks.Add
    
    Set db = CurrentDb
    Set rsDriver = db.OpenRecordset("Select * from TableList where TableList.Name_chk=Yes")  'new

    Do Until rsDriver.EOF
        ThisTable = rsDriver![TableName]
        NameSheet = rsDriver![SheetName]
        Set rsSrc = db.OpenRecordset(ThisTable)
        If Not rsSrc.EOF Then
            Set wsDest = wbDest.Worksheets.Add
            wsDest.Name = NameSheet
            For i = 1 To rsSrc.Fields.Count   ' this loop puts in the field names.
                wsDest.Cells(1, i) = rsSrc.Fields(i - 1).Name ' field numbers start at zero, excel starts at 1
            Next i
            wsDest.Range("A2").CopyFromRecordset rsSrc
    
    End If
        rsDriver.MoveNext
        Loop
    
    Set Fdia = FileDialog(msoFileDialogSaveAs)
    With Fdia
        .InitialFileName = Me.txtPath & Me.txtFile
    
        If .Show Then
            FilToSave = .SelectedItems(1)
        End If
    End With

    wbDest.saveas FilToSave

    Set wbDest = Nothing
    Set wsDest = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Set rsDriver = Nothing
    Set rsSrc = Nothing

End Sub

thanks

 

[lookforsmt]

HI! can anyone suggest me in which line do i need to enter the below error handler. I am getting 1004 error nos as mentioned in the previous post

On Error GoTo err_handler
Exit Sub
err_handler:

MsgBox Err.Description, vbExclamation, Err.Number
Exit Sub

thanks

 

[isladogs]

Hi

Error handling code is normally used like this:

Private Sub cmdExport_Click()

On Error GoTo Err_Handler

'ALL OTHER PROCEDURE CODE GOES HERE

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & err.Number & " " & Err.Description, vbExclamation, "Program error"
      GoTo Exit_Handler

Exit Sub

Hope you're making progress with your project

 

[lookforsmt]

HI! ridders

This is the last huddle of my race and i am still not able to cross over the finish line.
i have followed the error handler as mentioned. I am getting some of the below errors when trying to place the code on various lines as to check which line it fits:

It does not show up the save dialog popup Or
It saves excel with read only and have to close the excel program from control panel Or
It shows up the save dialog popup but does not save the excel Or
There is no change and runtime error still displays.

it may be simple but looks very tricky to me.

Will you be kind enough to pls have a look into.
Thanks