none
Relink Excel Tables via VBA - Access 2013 RRS feed

  • Question

  • All,

    I am following the directions in this original post:  (not allowed to post link, but it's from this forum, and the subject is "Relink Tables(Excel) via VBA")

    The difference between the original user and I:  I'm assuming that my user has placed the DB into a new folder along with the Excel templates, and now needs to relink the paths.  I'm using currentproject.path as a way to get that connection established.  I'll paste the code at the bottom.  (Using a button click during the testing process using an unbound form with an unbound button)  This is probably the tenth set of code I've tried from around the forums, and this is the first code that has resulted in a reference to the new location! 

    Unfortunately, I'm getting the error:  Run-Time Error '3051':  The Microsoft Access dabase engine cannot open or wrote to the file (current file location I want to remap to).  It is already opened exclusively by another user, or you need permission to view and write its data.

    Any ideas?  I'm the only user, and permissions on the network location set me as the owner.  (this is also why we need to the data moved when users publish the data for their use)

    Private Sub Command0_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
     
    Dim strOldConnect As String
    Dim strConnectComponent As String
    Dim strDatabaseComponent As String
    Dim strNewConnect As String
     
    Set db = CurrentDb
     
    strOldConnect = db.TableDefs("CmpMileageOnlyVerifReport").Connect
    strConnectComponent = Left(strOldConnect, InStr(strOldConnect, ";DATABASE"))
    strDatabaseComponent = CurrentProject.Path
    strNewConnect = strConnectComponent & "DATABASE=" & strDatabaseComponent
     
    Set tdf = db.TableDefs("CmpMileageOnlyVerifReport")
    tdf.Connect = strNewConnect
    tdf.RefreshLink
     
    Set tdf = db.TableDefs("CmpPayOrdsWhereTvlInDtsReport")
    tdf.Connect = strNewConnect
    tdf.RefreshLink
     
    Set tdf = db.TableDefs("Detail - All Columns")
    tdf.Connect = strNewConnect
    tdf.RefreshLink
     
    'Application.RefreshDatabaseWindow '
     
    Set tdf = Nothing
    Set db = Nothing
     
    End Sub

    Tuesday, April 19, 2016 1:08 PM

Answers

  • Here is how to loop through all Excel files in a folder and create links to all.


    Private Sub Command1_Click()
    
     Dim strPathFile As String
     Dim strFile As String
     Dim strPath As String
     Dim strTable As String
     Dim blnHasFieldNames As Boolean
    
     ' Change this next line to True if the first row in CSV worksheet
     ' has field names
     blnHasFieldNames = True
    
     ' Replace C:\Documents\ with the real path to the folder that
     ' contains the CSV files
     strPath = "C:\Users\Source_Files\"
    
     ' Replace tablename with the real name of the table into which
     ' the data are to be imported
    
     strFile = Dir(strPath & "*.xl*")
    
    
     Do While Len(strFile) > 0
           strTable = Left(strFile, Len(strFile) - 4)
           strPathFile = strPath & strFile
    
           ' Link CSV Files
           ' DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
    
           ' Import Excel Files
           DoCmd.TransferSpreadsheet , , strTable, strPathFile, blnHasFieldNames
    
    
     ' Uncomment out the next code step if you want to delete the
     ' EXCEL file after it's been imported
     '       Kill strPathFile
    
           strFile = Dir()
    
     Loop
    
    End Sub


    If, for instance, you wanted to loop through CSV files and link to all, just un-comment this line of code.

    ' DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


    And put a comment in front of this line of code.

    ' DoCmd.TransferSpreadsheet , , strTable, strPathFile, blnHasFieldNames

    One more thing, you may find this link very helpful.

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm


    MY BOOK



    Tuesday, April 19, 2016 7:32 PM

All replies

  • Here is how to loop through all Excel files in a folder and create links to all.


    Private Sub Command1_Click()
    
     Dim strPathFile As String
     Dim strFile As String
     Dim strPath As String
     Dim strTable As String
     Dim blnHasFieldNames As Boolean
    
     ' Change this next line to True if the first row in CSV worksheet
     ' has field names
     blnHasFieldNames = True
    
     ' Replace C:\Documents\ with the real path to the folder that
     ' contains the CSV files
     strPath = "C:\Users\Source_Files\"
    
     ' Replace tablename with the real name of the table into which
     ' the data are to be imported
    
     strFile = Dir(strPath & "*.xl*")
    
    
     Do While Len(strFile) > 0
           strTable = Left(strFile, Len(strFile) - 4)
           strPathFile = strPath & strFile
    
           ' Link CSV Files
           ' DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
    
           ' Import Excel Files
           DoCmd.TransferSpreadsheet , , strTable, strPathFile, blnHasFieldNames
    
    
     ' Uncomment out the next code step if you want to delete the
     ' EXCEL file after it's been imported
     '       Kill strPathFile
    
           strFile = Dir()
    
     Loop
    
    End Sub


    If, for instance, you wanted to loop through CSV files and link to all, just un-comment this line of code.

    ' DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


    And put a comment in front of this line of code.

    ' DoCmd.TransferSpreadsheet , , strTable, strPathFile, blnHasFieldNames

    One more thing, you may find this link very helpful.

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm


    MY BOOK



    Tuesday, April 19, 2016 7:32 PM
  • Hi KimberlyMix

    I try to solve the error in your code but I get a different error then you.

    then I run the code provided by the ryguy72.

    it can solve your issue. please check it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 20, 2016 9:57 AM
    Moderator