UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exporting Multiple Access Tables to Excel at One Time    
 
   
mrscomp
post May 12 2010, 03:10 PM
Post#1



Posts: 19
Joined: 15-November 05



Hello everyone! Been a while since I've been here, I see things have changed. Looks nice!
I am trying to find out if there is a way to export multiple Access tables to Excel format all at once. Ideally I'd like to be able to select which tables to export then have them export to the same file with a separate tabe for each table, but I'll take whatever is possible. I've done some searching on it, and it seems that there are some answers about it that involve using VBA coding. Unfortunately, my knowledge of VBA coding is just slightly above knowing that it exists - I can read the simpler stuff, and write extremely easy stuff using the autofill features.
Does anybody have a clear way of doing this, and also is patient enough to deal with me?
Go to the top of the page
 
Bleuspam
post May 12 2010, 04:02 PM
Post#2



Posts: 560
Joined: 16-March 07
From: Pennsylvania


The VBA required to do this is also pretty simple.
irst ... how do you want to launch the VBA code to do this? Do you have a form with a button that you'll be clicking?
If that sounds like a good solution, create a blank form. Next create a button. If a wizard pops up asking you what the button is for, just Close it. We don't need the wizard. <
Now in Design view of the form, you right click the button and choose "Build Event" That opens up the editor where we can add some code.
It probably begins the coding for you by adding something that looks like this:
CODE
Private Sub Button1_Click()
End Sub

Anything inside of those wraps will execute whenever the button is clicked. Now I have some code that does the same thing that you are asking, so I'll just add what I have:
CODE
strPath = "V:\Reports\Worklist_Summary.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryEscByDate", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCreatedByDate", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryClosedByDate", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCreatedByUsers", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qrySummaries", strPath

That code just exports all of those queries to the same spreadsheet (the path is defined up at the top).
This should give you a good intro.
(1) You'll have to modify the part acSpreadsheetTypeExcel12 if you don't have Excel 2007. In fact, start typing a new line and as you type through DoCmd.TransferSpreadsheet and hit the space bar, the editor will actually show you the options of what it is expecting! Otherwise just google or ask and we can help you figure out the arguments for your version of Excel.
(2) "qryEscDate" is just an example of my query. Yours will be the table name. You treat tables and queries the same, they are both objects. So just type your table names in each line. Access automatically makes the Excel tab (worksheet) name as the table/query name.
(3) strPath is a variable that we define at the top as the name of the spreadsheet. You could type the path in each line of code individually, but it saves time for us to define it one time and then we only have to say strPath and Access knows what that means. <
Go to the top of the page
 
20 Ton Squirrel
post May 12 2010, 04:08 PM
Post#3



Posts: 86
Joined: 11-May 10
From: Houston, TX


You can right-click on a table/query and export each to the same Excel file, this will generate each in a separate tab.
therwise you'll need some fancy coding! This will be a little complex, so bear with me.
On a form, slap down a list box and a button.
Right-click on the list box, select Properties.
On the OTHER tab of the Properties dialog, set Multi Select to Extended.
Select the button, go to the Event tab on the Properties dialog. Select [Event Procedure] and click the ... button to the left of it.
This will open the code window. Copy and paste this over the ENTIRE page of code you see:
CODE
Option Compare Database
Option Explicit
Private Sub Command2_Click()
  Dim strFile As String
  Dim varItem As Variant
  
    strFile = InputBox("Designate the path and file name to export to...", "Export")
    
    If (strFile = vbNullString) Then Exit Sub
    
    For Each varItem In Me.List0.ItemsSelected
        DoCmd.TransferSpreadsheet transferType:=acExport, _
                                  spreadsheetType:=acSpreadsheetTypeExcel9, _
                                  tableName:=Me.List0.ItemData(varItem), _
                                  fileName:=strFile
    Next
    
    MsgBox "Process complete.", vbOKOnly, "Export"
End Sub
Private Sub Form_Open(Cancel As Integer)
  Dim strTables As String
  Dim tdf As TableDef
  
  
    For Each tdf In CurrentDb.TableDefs
        If (Left(tdf.Name, 4) <> "MSys") Then
            strTables = strTables & tdf.Name & ","
        End If
    Next
    strTables = Left(strTables, Len(strTables) - 1)
    
    Me.List0.RowSource = strTables
End Sub

Close the code window, save the form. Open the form in normal mode.
The list box should populate with your list of tables. If you click on the button it will prompt you for a path and file name (such as C:\exports\myExport.xls). If you click CANCEL it will abort, but if you input something it will export all the selected tables to the same Excel workbook.
I've attached an example so you can piece through it. Feel free to pester me further if you need. <
Attached File(s)
Attached File  multiTableExport.zip ( 16.2K )Number of downloads: 595
 
Go to the top of the page
 
20 Ton Squirrel
post May 12 2010, 04:12 PM
Post#4



Posts: 86
Joined: 11-May 10
From: Houston, TX


Kudos for beating me to the punch, Bleuspam, and for making a concise and simple solution.
Go to the top of the page
 
Bleuspam
post May 12 2010, 06:41 PM
Post#5



Posts: 560
Joined: 16-March 07
From: Pennsylvania


Excellent solution + example and suggestions. Your method is by far the best and more versatile!
Go to the top of the page
 
mrscomp
post May 17 2010, 12:52 PM
Post#6



Posts: 19
Joined: 15-November 05



Thanks Bleuspam and 20 Ton!
've been pulled to another project for the moment, but will be getting back to this shortly. I'll let you know how it goes!
Now, do you have any code that can clone me so I can work on everything at the same t
Go to the top of the page
 
Bleuspam
post May 17 2010, 02:21 PM
Post#7



Posts: 560
Joined: 16-March 07
From: Pennsylvania


I managed to perform enough Yoga to get intouch with my inner self and I saw the code upon which my mind runs:
CODE
Dim Caffeinated as Necessary
Do While Me.Awake = False
   DoCmd.Sit
   DoCmd.SipCoffee(Caffeinated)
Loop
Go to the top of the page
 
Sami
post May 23 2019, 07:09 AM
Post#8



Posts: 33
Joined: 3-April 19



I am sorry but nothing got work.
Go to the top of the page
 
Sami
post May 24 2019, 05:42 AM
Post#9



Posts: 33
Joined: 3-April 19



I got it
CODE
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
  Dim strTables As String
  Dim tdf As TableDef
  
  
    For Each tdf In CurrentDb.TableDefs
        If (Left(tdf.Name, 4) <> "MSys") Then
            strTables = strTables & ";"& tdf.Name
        End If
    Next
    
    
    Me.List0.RowSource = strTables
End Sub

Thank you greatly
This post has been edited by Sami: May 24 2019, 05:43 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 04:14 PM