Combined Export (1 Viewer)

[Ben_Entrew]

Hi all,

I'm getting desperate here.
I want to export files these files BP_SUMMARY,BP_fix,BP_var,
QT_SUMMARY,QT_fix,QT_var, TEF_SUMMARY,TEF_fix,TEF_var.
At first I want to select an export location and then I want to create files like

REPORT_BP,REPORT_QT, REPORT_TEF

REPORT_BP shall include BP_SUMMARY,BP_fix and BP_var.
Same for the rest.

Is there a way to do this in vba?

Thanks in advance.

Regards,
Ben

 

[Uncle Gizmo]

I don't get it?

 

[Ben_Entrew]

Hi, there are three file belonging to a unit BP, QT or TEF. I want to export them together as REPORT_BP, REPORT_QT or REPORT_TEF. when I select an export file name and then export all these tables it will create tabs wihtin my selected export file name. But I want to let the user select a folder location at first and then create these above mentioned REPORT files. Hope now my issue is getting more clearer.

 

[Mile-O]

Let's try and break this down...

You have an Access database. You want to export something to Excel. You want to create three spreadsheets via VBA that will hold the exported information. Each spreadsheet will have three tabs, comprising Summary, Fix, and Var.

If this is right, then what are we exporting? Is it a query in your database or is it an actual report?

 

[Ben_Entrew]

Hello Mile-O,
actually these are tables in my database.

 

[Mile-O]

Better to use queries on your tables. That way you can do some sorting, extra formulas, counts, or exclude unnecessary fields.

Without getting too in depth with VBA and opening up the Excel object for coding with, you could make use of the TransferSpreadsheet method, whether that be with macros or VBA.

 

[JHB]

...But I want to let the user select a folder location at first ...

Below is some code for selecting a folder, (you need to set references to "Microsoft Office Object library"):

Function GetFolder(strPath As String) As String
  Dim fldr As FileDialog
  Dim sItem As String
  Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
  With fldr
      .Title = "Select a Folder"
      .AllowMultiSelect = False
      .InitialFileName = strPath
      If .Show <> -1 Then GoTo NextCode
      sItem = .SelectedItems(1)
  End With
NextCode:
  GetFolder = sItem
  Set fldr = Nothing
End Function

Private Sub Command0_Click()
   'Start the dialogbox in 
   GetFolder ("C:\")
End Sub

 

[Ben_Entrew]

Hi all,

I wrote down the following code:

  Public Sub TEST_Daily_ExportTables()     
        Dim tdf As DAO.TableDef
        For Each tdf In CurrentDb.TableDefs
            If tdf.RecordCount > 0 Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
                    tdf.Name, folderlocation & "REPORT" & ".xls", -1
           
              
            End If
        Next
    End Sub

How can I collect these SUMMARY,fix, variable tables into one export file?
And then create QT,TEF BP export files after each loop run.

Thanks in advance.

Regards,
Ben