Open an Excel Spreadsheet through Access

[Guest]

I have 30 odd spreadsheets that i need to import into an access database
every week, an end user will actually be doing it and i need to fully
automate it.

Firstly each spreadsheet has links, so before it is imported it needs to be
opened and then saved.

I would like the user to be able to type the name of the file in an input
box and then it opens the file.

I have tried access' own wizards to open excel (doesnt work), open
application (says invalid call or procedure). I've also tried bits of code
from other threads:

Private Sub cmdFunction1_Click()
Dim xlApp, xlBookFormatted As Object
Dim strPlanner As String
strPlanner = InputBox("Enter Planner Initials")
Set xlApp = CreateObject("Excel.Application")
Set xlBookFormatted = xlApp.Workbooks.Add("\\birdat02\fsshare1$\Management
Information\Planner Trackers\Formatted Trackers\FT - " & strPlanner & ".xls")
xlApp.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
End Sub

This works but opens the file with a 1 after the name and will not then link
properly. Also tried:

Dim stAppName As String
stAppName = "\\birdat02\fsshare1$\Management Information\Planner
Trackers\Formatted Trackers\FT - APR.xls"
params = """" & stAppName & """"
excelplace = "C:\Program Files\Microsoft Office\OFFICE11\excel.exe"
excelplace2 = """" & excelplace & """"
Call Shell(excelplace2 & params, 1)

which i kind of borrowed from another newsgroup answer but this only opens a
blank worksheet.

If that wasn't enough, if possible, i would like once it has opened to use
'send keys' or similar to automatically press the 'Update links' popup that
comes up and then once it has finished linking, save the workbook on top of
itself, preferably without it asking if i want to overwrite it.

Any help would be appreciated.
Thanks
Emma

 

[George Nicholson]

Untested aircode:

1) Try using the Workbooks.Open method, and its "UpdateLinks" argument.
That argument is set to 3 below: "Update both remote and external
refs". Check Excel VBA Help if that isn't what you want.
(BTW: Workbooks.Add creates a new workbook using the specified file (if
there is one) as a template. In your case, since you specified an existing
..xls file rather than a .xlt file, the new .xls file has a 1 appended to its
name because Excel won't create a 2nd file with the same name.)

Set xlApp = CreateObject("Excel.Application")
Set xlBookFormatted = xlApp.Workbooks.Open("\\birdat02\fsshare1$\Management
Information\Planner Trackers\Formatted Trackers\FT - " & strPlanner &
".xls",3)

2) Use the Save method to save changes, then Close (without saving changes,
since you know you just did that)

xlBookFormatted.Save
DoEvents ' I like to add DoEvents just to be sure that the Save executes
cleanly before proceding. Probably overkill, but it doesn't hurt.

xlBookFormatted.Close SaveChanges:=False

HTH,

 

[Guest]

George,

Can't thank you enough. This worked perfectly and fixed all my problems and
your answer was so clear, it was very easy for me to fix.

Thanks you
Emma

 

[George Nicholson]

That's our motto: "Fixing the world: one app at a time."

Glad to help.