Question Excel Spreadsheet in Access

[DrZac]

Apologies if this is in wrong section but I can't see which it fits into.

I have a database (using 2007) for which I manage my practice; client details etc etc. I also have an Excel spreadsheet which stores details of statutory returns filed such as VAT etc. and this is displayed as clients in 1 column, then months in subsequent columns (Jan, Feb, Mar and so on). Once I have completed a return for that month I simply colour the cell green. I use the same process for accounts that have been filed (on separate worksheet) and various other monthly tasks.

What I would like to be able to do is manage this spreadsheet from within Access; i.e. not having to load Excel. So I simply click a button on the Dashboard and up pops the sheet and then close it to return to Dashboard.

Is this possible?

**please bear in mind that the poster of this question is a complete Novice when it comes to Access and technical terms tend to cause mass confusion.

 

[Trevor G]

On your dashboard you can add a command button to open the workbook, all you need is a little code:

Please try this. OPen your dashboard form in design view, then look for the commandbutton wizard and click and then click in the form somewhere, when the wizard starts click cancel. Then open the properties of the button and look at the Event Tab and click on On Click and then look at the Ellipse button to the right and once clicked select Event Procedure. This takes you into the VBA screen, You then need to look to set the Reference to use Excel.
Select the Tools Menu
Select References
Search down for Microsoft Excel XX. Object Library (XX is the version number)
then Highlight the following code and copy it and paste it into the On Click Event, all you will need to do is change the path to where the workbook is kept and the workbook name:

Dim xlApp as Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbook.Open "Enter the full path and workbook name in here"
.Sheets("Which sheet to open").Select
End With


Save the Code and close the VBA screen down, switch the view to Form View and Click your button and Excel will open.

 

[DrZac]

Many thanks for your quick response Trevor; I will give that a go when I get into the office in the morning

 

[DrZac]

Finally managed to get that VBA code after several weeks of updating, changing and adding to the initial database.

Only problem is, it launches excel but does not open the spreadsheet; it returns with a Runtime Error 438 and on the debug:-

.Workbook.Open "c:\ClientReturns.xlsx"

is highlighted as the problem. I have selected and ticked the Microsoft Excel 12 object library - am I missing something here?

Full code for the event is:-

Private Sub Command39_Click()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbook.Open "c:\ClientReturns.xlsx"
.Sheets("VAT").Select
End With

xlApp.UserControl = True
End Sub

Thanks.

 

[Trevor G]

You need a "s" after workbook

workbooks.

 

[DrZac]

As simple as that? Many thanks.

 

[Trevor G]

Your welcome and thanks for replying.