Results 1 to 11 of 11
  1. #1
    mcorley is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    6

    Importing Excel Files, and then exporting Access Tables to Excel

    Hello all,

    Import Issue:

    I am trying to figure out how to import data from a specific worksheet in all excel files in a single folder into separate tables via transferspreadsheet (VB). The problem I am facing is that the below will not work. Can anyone help me edit this?

    Option Compare Database
    Private Sub Command0_Click()


    Dim blnHasFieldNames As Boolean
    Dim strWorksheet As String, strTable As String
    Dim strPath As String, strPathFile As String

    blnHasFieldNames = True
    strPath = "C:\Users\"
    strWorksheet = "Data"
    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)
    DoCmd.TransferSpreadsheet acImport, _
    acSpreadsheetTypeExcel9, strTable, strPathFile, _
    blnHasFieldNames, strWorksheet & "$"

    strFile = Dir()
    Loop
    End Sub


    Export Issue:

    I have some code below that will not work. I am trying to display a dialogbox to select a table within a database and then export that table to an excel spreadsheet with the same table name (excluding the .mdb extension). Can anyone help edit this code?Private Sub Form_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    fDialog.Filters.Add "All Files", "*.*"

    If fDialog.Show = True Then
    For Each varFile In fDialog.SelectedItems
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    "MyNewSpreadsheet", varFile, True
    Next
    End If
    End Sub

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Attached is something I have used in the past. The difference is there is an intermediate step to join the two tables and then export them from a query. You can modify that however, but this will give you a starting point.
    Attached Files Attached Files

  3. #3
    mcorley is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    6
    Thanks Alan,

    I still want somehow to allow the user to select the excel files they want to import and then the tables they want export into excel using fDialog. Anyway to alter the code to do that?

    Also I do not need to join sheets, but rather create separate tables for a specific sheet in each excel file that has the name "Data" Any help on this?

    Regards,

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    For the names of the excel sheets you wish to import, you could put an unbound text box on your form. Have the users input the file name and path. Use that in your code for the files to import.

    For the exportation, use the same concept. An unbound text box with the name of the table, then amend your code to look at the unbound text box to export.

  5. #5
    mcorley is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    6
    Alan,

    I have unbound text box on my form now, but what is the code to do what you mentioned above?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is this code in the same database where the import/export tables are located?

    Import Issue:

    Why doesn't work - error message, wrong results, nothing happens?

    Export Issue:
    FileDialog does not allow selection of tables within a database. This is a utility to navigate and locate files in the Windows environment. This could be useful in the Import procedure to locate Excel files.
    Use a combobox on a form that has a list of tables as RowSource.
    Reference the combobox in code.
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me.combobox, theFilePath, True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mcorley is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    6
    Thanks Alan and June7,

    Import Issue:
    This code works now (below), however, I am still trying to have the code select all excel files within a selected pathway file folder and with the same worksheet name( "Data" within all files) and then rename all tables with each excel file name. Any tips to doing this?

    Private Sub Command1_Click()

    Dim fDialog As Office.FileDialog
    Dim varFile As Variant

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    fDialog.Filters.Clear
    fDialog.Filters.Add "Excel Spreadsheets", "*.xlsx; *.xls"

    If fDialog.Show = True Then
    For Each varFile In fDialog.SelectedItems
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "tbl_Data", varFile, True
    Next
    End If
    End Sub

    Export Issue:
    I am still using a form to export one table at a time, but I want to export all tables available in the database as excel files with same file name (excluding the _tbl and .mdb extensions etc.). The colde below runs only after I have generated a query labeled "Data" and then export the file name as PizzaData onto my desktop...Any simpler way to do what I to do?

    Private Sub Command5_Click()
    On Error GoTo Err_Command5_Click
    Dim reportName As String
    Dim theFilePath As String
    reportName = "Data"
    theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Pizza"
    theFilePath = theFilePath & reportName & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True
    MsgBox "Look on your desktop for your Excel file labeled: ...Data"
    Exit_Command5_Click:
    Exit Sub
    Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click


    End Sub

  8. #8
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    For your export exercise, you should not have to create a query. Just use the table name for the variable reportName.

    Look here for the transferspreadsheet syntax http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

    As for multiple exports, look at this link.

    http://www.utteraccess.com/forum/Exp...-t1944631.html

  9. #9
    mcorley is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    6
    Through a lot of altercations I made the form do what I wanted it to do. Now all I have to do code in the refresh database command to make sure my listbox of tables/forms appears correctly. Any ideas for this?

  10. #10
    mcorley is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    6
    I figured it out. Thanks.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    For the import, the Excel files are not all in one folder but can be scattered throughout various subfolders and there would be other files in those folders?

    You want to search all the subfolders? Review http://www.ammara.com/access_image_f...er_search.html

    Then maybe a condition to determine if file has the correct name:

    If varFile.Name Like "Data*.xls*" Then DoCmd...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-09-2012, 06:20 PM
  2. Replies: 6
    Last Post: 12-03-2012, 08:08 AM
  3. Importing Excel Data Tables into Access
    By Itch in forum Programming
    Replies: 5
    Last Post: 09-08-2012, 11:27 AM
  4. Replies: 1
    Last Post: 08-22-2011, 02:09 PM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums