Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-28-2017, 12:50 AM   #1
Morten
Newly Registered User
 
Join Date: Sep 2009
Posts: 53
Thanks: 5
Thanked 0 Times in 0 Posts
Morten is on a distinguished road
Auto refresh linked Excel sheet with most recent file

Hi,

We have a database with a linked Excel file.

The Excel file is saved on daily basis with a new file name, so right now we have a folder with lots of files with names like "Alle_20170928.xlsb".

The path to the folder is P:\CK\Lukkede Mapper\POWL\.

How can I refresh the linked Excel file with the most recent file?

Best regards
Morten

Morten is offline   Reply With Quote
Old 09-28-2017, 01:11 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,861
Thanks: 92
Thanked 1,689 Times in 1,568 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Auto refresh linked Excel sheet with most recent file

Have a look at this post. You should be able to adapt it for your needs

https://www.access-programmers.co.uk...7&postcount=34
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Morten (09-28-2017)
Old 09-28-2017, 04:03 AM   #3
Morten
Newly Registered User
 
Join Date: Sep 2009
Posts: 53
Thanks: 5
Thanked 0 Times in 0 Posts
Morten is on a distinguished road
Re: Auto refresh linked Excel sheet with most recent file

Thanks a lot.

Morten is offline   Reply With Quote
Old 09-28-2017, 05:03 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Auto refresh linked Excel sheet with most recent file

before using this function, backup your
db first.

i have created a function that will
"Refresh" your Excel link Table.
actually you cannot really "refresh"
the link without deleting the
Link table first and then
creating new Link table.

the function RefreshExcelLink() can
be called within AutoExec macro
or on Timer Event of a Form.
besure that when you call this function,
the Link Excel file is closed and
not in used.

to call:

RefreshExcelLink("nameOfLinkExcelFile")

paste the code in a Standard Module.

'******
AGAIN backup your db first to test this.
if you are confident that it will do
the work, do as you pleased.

Code:
Public Function RefreshExcelLink(ByVal strLink As String)
'**************************************************
'*
'* strLink is the name of Excel Link table
'* in Navigation Pane
'*
'**************************************************
    Dim td As TableDef
    Dim db As DAO.Database
    Dim strFile As String
    Dim strConnect As String
    Dim strSource As String
    Dim strPath As String
    Dim strExt As String
    
On Error Resume Next
    Set db = CurrentDb
    Set td = db.TableDefs(strLink)
    '* get original sourcetable and connection string
    strSource = td.SourceTableName
    strConnect = td.Connect
    
    '* close the link file
    Set td = Nothing
    
    '* remove portion of connection string
    strPath = Mid(strConnect, InStrRev(strConnect, "DATABASE="))
    strConnect = Left(strConnect, Len(strConnect) - Len(strPath))
    
    '* extract the path of file to link
    strPath = Replace(strPath, "DATABASE=", "")
    '* extract the extension portion
    strExt = Mid(strPath, InStrRev(strPath, ".") + 1)
    '* extract the path to link (final)
    strPath = Left(strPath, InStrRev(strPath, "\"))
    
    '* get the last modified file
    strFile = LastModifiedFile(strPath, strExt)
    
    '* delete old link file
    db.TableDefs.Delete (strLink)
    
    '* create new link file
    Set td = db.CreateTableDef(strLink)
    With td
        .Connect = strConnect & "DATABASE=" & strFile
        .SourceTableName = strSource
    End With
    
    db.TableDefs.Append td
    Set td = Nothing
    db.TableDefs.Refresh
    Set db = Nothing
    Application.RefreshDatabaseWindow
    
End Function

Public Function LastModifiedFile(ByVal strPath As String, Optional ByVal strExt As String = "") As Variant

    Dim oLastFile As Object
    Dim oFile As Object
    Dim oFS As Object
    
On Error GoTo ExitFunction
    strPath = Replace(strPath & "\", "\\", "\")
    strExt = LCase(strExt)
    Set oFS = CreateObject("Scripting.FileSystemObject")
    
    For Each oFile In oFS.GetFolder(strPath).Files
        If strExt = "" Or strExt = LCase(oFS.GetExtensionName(oFile.Name)) Then
            If oLastFile Is Nothing Then
                Set oLastFile = oFile
            Else
                If oLastFile.DateLastModified < oFile.DateLastModified Then
                    Set oLastFile = oFile
                End If
            End If
        End If
    Next
    LastModifiedFile = strPath & oLastFile.Name
ExitFunction:
    Set oLastFile = Nothing
    Set oFile = Nothing
    Set oFS = Nothing
End Function
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-28-2017, 07:49 PM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Auto refresh linked Excel sheet with most recent file

here is another code, that
replace link excel file with
the latest, without deleting
the Tabledef.

Code:
Public Function RefreshExcelLink2(ByVal strLink As String)
'**************************************************
'*
'* strLink is the name of Excel Link table
'* in Navigation Pane
'*
'**************************************************
    Dim TD As TableDef
    Dim DB As DAO.Database
    Dim strFile As String
    Dim strConnect As String
    Dim strSource As String
    Dim strPath As String
    Dim strExt As String
    
On Error Resume Next
    Set DB = CurrentDb
    Set TD = DB.TableDefs(strLink)
    '* get original sourcetable and connection string
    strSource = TD.SourceTableName
    strConnect = TD.Connect
    
    
    '* remove portion of connection string
    strPath = Mid(strConnect, InStrRev(strConnect, "DATABASE="))
    strConnect = Left(strConnect, Len(strConnect) - Len(strPath))
    
    '* extract the path of file to link
    strPath = Replace(strPath, "DATABASE=", "")
    '* extract the extension portion
    strExt = Mid(strPath, InStrRev(strPath, ".") + 1)
    '* extract the path to link (final)
    strPath = Left(strPath, InStrRev(strPath, "\"))
    
    '* get the last modified file
    strFile = LastModifiedFile(strPath, strExt)
    
    '* create the new link file
    With TD
        .Connect = strConnect & "DATABASE=" & strFile
        '.SourceTableName = strSource
        .RefreshLink
    End With
    
    Set TD = Nothing
    DB.TableDefs.Refresh
    Set DB = Nothing
    Application.RefreshDatabaseWindow
    
End Function

Public Function LastModifiedFile(ByVal strPath As String, Optional ByVal strExt As String = "") As Variant

    Dim oLastFile As Object
    Dim oFile As Object
    Dim oFS As Object
    
On Error GoTo ExitFunction
    strPath = Replace(strPath & "\", "\\", "\")
    strExt = LCase(strExt)
    Set oFS = CreateObject("Scripting.FileSystemObject")
    
    For Each oFile In oFS.GetFolder(strPath).Files
        If strExt = "" Or strExt = LCase(oFS.GetExtensionName(oFile.Name)) Then
            If oLastFile Is Nothing Then
                Set oLastFile = oFile
            Else
                If oLastFile.DateLastModified < oFile.DateLastModified Then
                    Set oLastFile = oFile
                End If
            End If
        End If
    Next
    LastModifiedFile = strPath & oLastFile.Name
ExitFunction:
    Set oLastFile = Nothing
    Set oFile = Nothing
    Set oFS = Nothing
End Function

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
excel , import , linked table , recent file

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I auto-refresh a linked Excel table that has links? davemarco Tables 0 02-06-2015 08:17 AM
Access FE user auto copy most recent file AccessUser4 General 4 06-04-2013 06:56 AM
Copy over a csv file contents, to a new sheet in a excel file not opening csv file? j.smith1981 Excel 0 08-13-2010 03:39 AM
Refresh Data from Excel Sheet popohoma Modules & VBA 0 12-18-2007 02:39 AM
Linked Excel Sheet damiendad Queries 1 03-16-2004 06:18 AM




All times are GMT -8. The time now is 12:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World