| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 52952 | 2005-01-03 12:55:00 | Opening an Excel spreadsheet from an Access form | chiefnz (545) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 310253 | 2005-01-03 12:55:00 | Hi, I'm trying to create an Access form where I can have one central point to access all the relevant files I use at work. Most of the forms I use are Excel spreadsheets. Basically what I want to do is have a main form in MS Access with various command buttons on them. Clicking on the button opens a MS Excel spreadsheet and I can then change the info on the spreadsheet. I can't find how to do this in Access.. so far all I have got it to do is just open a blank Excel spreadsheet.... How do I link a button to a file so that it opens the file straight away for me to edit etc the data. cheers chiefnz |
chiefnz (545) | ||
| 310254 | 2005-01-03 15:32:00 | I presume you know the path rather than wanting a filesearch box to select the file. I added a command button and used the Access wizard to create the code to open the Excel app which I presume you already have done. Add the line oApp.Workbooks.Open ("C:\Test.xls") where the path to the file is within the brackets. eg: Private Sub Command42_Click() On Error GoTo Err_Command42_Click Dim oApp As Object Set oApp = CreateObject("Excel.Application") oApp.Visible = True 'Only XL 97 supports UserControl Property On Error Resume Next oApp.UserControl = True oApp.Workbooks.Open ("C:\Test.xls") 'change this to the path you want Exit_Command42_Click: Exit Sub Err_Command42_Click: MsgBox Err.Description Resume Exit_Command42_Click End Sub |
Parry (5696) | ||
| 310255 | 2005-01-03 16:00:00 | Well after a few hours of tinkering I've managed to get things going all I needed was to use the link option in the command button properties. Works a treat. Now all I need to figure out is how to open the form I designed without having to open MS Access in design mode.. that is as an application or *.exe file on it's own. cheers chiefnz |
chiefnz (545) | ||
| 310256 | 2005-01-03 16:58:00 | Linking a workbook is different to opening one. When you link its creating a new table so like any other table in Access you can build a form to view its contents. Its a bit vague what you require when your saying all your forms are in Excel. Do you mean a UserForm (ie you created a form using VBA) or do you mean just a sheet with data that your calling a form. I guess if its the later then your linking is probably enough. hth |
Parry (5696) | ||
| 310257 | 2005-01-03 18:10:00 | I used the form wizard in Access to design a user interface. So basically its a window with command buttons on it. When the user clicks on the command buttons then the relevant excel spreadsheet is opened. I hope that's a bit clearer. I'm doing a bit more work on the interface form but if seeing it will help then post back and I will put a screenshot up on ImageF1 cheers chiefnz |
chiefnz (545) | ||
| 310258 | 2005-01-03 20:14:00 | to open the form or to preform any other actions on loading of the database create a macro called "autoexec" set the actions etc and save. Now when ever the database is loaded it will open your form | beama (111) | ||
| 310259 | 2005-01-03 21:12:00 | Or in Access, look at Tools_Start Up Don |
donread (6401) | ||
| 310260 | 2005-01-04 00:46:00 | Hi again, A) If your wanting to view the linked table then... Create a form based on the linked table Open your user interface form in design mode Open the toolbox and draw a command button on your form. In the wizard that appears select the 'Form Operations' category and the 'Open Form' action and click Next the choose the form you created to the linked table and click Finish B) If your actually wanting to open the Excel file itself then... Open your user interface form in design mode Open the toolbox and draw a command button on your form. In the wizard that appears select the 'Application' category and the 'Run MS Excel' action and click Finish Right click the new command button and select Build Event and you should be taken to the VBEditor with code similar to what I posted above. Before the line Exit_Command6_Click: (Command6 may be a different name in your form) paste in a new line ... oApp.Workbooks.Open ("C:\Test.xls") Replace the C:\Test.xls text with the name of your path and filename then save and close the VBE. hth |
Parry (5696) | ||
| 1 | |||||