E
Emma Hope
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
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