Linking Excel To Access Automatically

[dr4ke]

Afternoon all,



Having experimented with Access for a few months now; I know you can link an Access Database to SharePoint, and you can export Excel files into Access for inclusion in tables.

My question is; If I have a daily report being produced, through script, with an excel output. Is there anyway to automatically publish this in Access, negating the need to manually go into Access to import?

Hope the above explains my situation enough, but if not please say and i'll try to provide more clarity.

Regards,

[June7]

What do you mean by 'publish'? Do you want to just link to the Excel worksheet or do you want to import records? You want that to be accomplished without opening the Access file? I am sure this can be done by VBA behind Excel or another Access file but no idea about your 'script'.

[dr4ke]

Thank you for your reply; here is an example of what I am hoping to achieve, which will hopefully provide additional clarity.

Scenario

Every day a script is run and a file is saved on a Shared Drive (S:\Reports\Scripted\Daily), each day is a new file. For the first 5 days in the month of December 2012 the below reports will be present on the above location;

1) 2012-12-01_DailyReport
2) 2012-12-02-DailyReport
3) 2012-12-03-DailyReport
4) 2012-12-04-DailyReport
5) 2012-12-05-DailyReport

In a normal circumstance; you would have to open Access, and 'import from Excel' and chose the new file. Obviously this would be a manual effort every single day.

My questions is; is there a way to automate that process, without opening Access, so the daily reports get automatically imported into the specified table?

[June7]

None that I know of. I have tried. Best I could do is a VBScript to automate opening Access then code behind the default open form runs. I use Windows Task Manager to run the VBScript every Monday. If there is a way to run an Access procedure from VBScript, I couldn't find it. Build the script in Notepad, save, change the file extension to vbs. Here is the script:

Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill
strComputer = "."
strProcessKill = "'msaccess.exe'"
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
objProcess.Terminate()
Next
Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""\\dotatufs02\CRM\Lab\Database\Program\Editing\Ma terialsDatabase.accdb"""
End If

[dr4ke]

Thanks June,

Sorry; not great with VBScript... What does this do and what do I need to change?

[June7]

The VBScript is an executable that will first terminate any running Access process and then open the named database. That's all. Change file path/name for your database. Also, the date/time conditions I use for running the script are probably not applicable to you. So change the If Then or eliminate.

The \\dotadufs02 shown in script is pathing with UNC (Universal Naming Convention). This is the server name instead of static mapped drive letter pathing (such as H:\filename.accdb).

This script is installed only on my computer and the TaskManager only on my computer is set to run this script. Code in the database determines that the frontend is opening under my network login and then executes VBA procedure.