Results 1 to 4 of 4
  1. #1
    roya is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    2

    Question Update exported query to excel by Overwriting data in existing excel file and not replace the file


    I have an ms access database that keeps track of many logs for us. The backend tables are stored on a SharePoint site document location so 3 front end users ( data entry) (front end files stored locally on users computers) can Input data for the rest of the workers to view via exported queries. I have multiple queries updated every time their associated tables are updated. I could only accomplish these exports, to replace an existing file (on SharePoint site document that is synced to data entry user computer, and from there it uploads to SharePoint cloud where everyone else can access the file). Problem is that exported files do not have the proper formatting I need for the end users to be able to affectively see the data ( freeze header row, auto filter on columns etc. ) Ideally I want to have these exported queries be updating the data in an existing excel table. This way I can format the excel tables once and that formatting will keep through every update.
    Note: the computer that generates the export query and the end user of the exported query are two different users and computers both having access to the same SharePoint document folder/ file.

    This is not an ideal database or SharePoint configurations. Expert tips on both ends to resolve this problem is much appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    I don't know SharePoint - never used.

    I do know automation code to manipulate Excel file. Using automation, can open Excel objects, clear data from spreadsheet, write new data. Example of automation http://www.accessmvp.com/KDSnell/EXC...m#WriteRstFile

    Alternatively, Excel pulls data from Access.
    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.

  3. #3
    roya is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    2
    Thank you. This did solve the problem. My only question is now that it takes longer to run than my previous method, any suggestion for improved runtime?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Maybe eliminate Excel component? Why do you need Excel?
    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: 1
    Last Post: 10-25-2016, 03:24 PM
  2. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM
  3. Replies: 1
    Last Post: 08-07-2014, 10:04 AM
  4. export data to existing excel file
    By joshynaresh in forum Import/Export Data
    Replies: 7
    Last Post: 01-27-2014, 10:57 PM
  5. Linking Workbook and Overwriting Excel File
    By Atheron in forum Import/Export Data
    Replies: 2
    Last Post: 10-08-2011, 03:44 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 - Senior Forums