# MS Access macro to open an Excel Spreadsheet



## michael_j_allen (Sep 28, 2009)

I need an MS Access macro to open an Excel Spreadsheet. Can anyone out there please help?


----------



## Dragoen (Apr 10, 2009)

Hi Michael,

Couple of questions. What version of Access are you using? Do you want to just open the Excel spreadsheet, or do you want to open the spreadsheet and either read data from it or write data to it?


----------



## michael_j_allen (Sep 28, 2009)

Hello Dragoen,

Good questions. I asked the question because at the moment I am interested in opening the spreadsheet to read data from it. 

Looking forward to hearing from you soon.

Regards,

Mike


----------



## Dragoen (Apr 10, 2009)

Sorry for the delay in getting back to you, work got busy. I had hoped you would have stated what version of Access you were using.

With Access 2003 you can use the TransferSpreadsheet macro Action. That action is further customizable by setting its properties which are fully explained in the Access Help file (F1). TransferSpreadsheet covers Importing data, Exporting data, and establishing a link to the spreadsheet or Named range.

In Access 2007, select External Data from the main menu, hover over Excel and hit F1. This help topic provides detailed instructions for working with spreadsheets, creating import specifications, and explaining all the options.

Some general rules for importing data: Access is limited to 255 fields per table so your spreadsheet or Named range is limited to 255 columns. Access can only import one sheet at a time, you'll have to run the process for each sheet in a workbook. You can create re-useable specifications for importing or exporting so you only have to define the spreadsheet one time.

I don't know what changes MS might have made for Access 2010, haven't had a chance to work with that version yet.

Hope that helps,


----------



## michael_j_allen (Sep 28, 2009)

Hi Dragoen,

Thanks for your response. I worked on the TransferSpreadsheet command using the link option and sure enough it created a linked table with the name I chose in my database. The only problem was that the linked table operated differently from the opened spreadsheet.

You see, the spreadsheet creates a vector of random entries in a column which changes each time the spreadsheet is opened using excel, which is what I want. The linked table does not do this, showing the same vector with each successive open.

What do you think of using the runApp command instead?
My only problem here is that I need a way to specify both paths as parameters in the macro since I have to install this application on several different computers and I cannot predict the location of the targets always.

Do you have any ideas?

Looking forward to hearing from you.

Regards,

Mike


----------



## Dragoen (Apr 10, 2009)

Mike,

The TransferSpreadsheet function isn't going to give you the ability to open a spreadsheet within Access and use the sheet as you would if you had opened it in Excel. Access basically sees the rows as data records and the columns as data fields. Access looks at the value in the field but not the formula. It's kind of a snapshot, but being linked you can change the data values in the cells and other Access controls can reference the cells as if the spreadsheet were another table in the Access database.

The RunApp function is a way to start an Excel session from within Access but does not link to the workbook that you open. You also have the same problem identifying the path\filename of the workbook to open.

As far as being able to specify a path to a workbook, you would need to create an Access form that would provide the user a navigation control to go select the workbook from where ever it is stored. That path information is then stored in a parameter that is used in the Macro definition in place of a hard path\filename entry. Now you are getting into vba coding and a user form.

Hope that better explains your options.


----------



## michael_j_allen (Sep 28, 2009)

Thanks again Dragoen,

I really want to generate this application so I do not mind going down the VBA route if I have to. Tell me, is there a place where I can find all those VBA choices explained? My immediate concern is for a means of supplying path information to the form or whatever else.

My steps would be as follows:-

1.	Call on excel to open the spreadsheet thereby generating the new random vector. (2 paths needed)
2.	Let excel save the revised spreadsheet as a tab delimited text file already known to the application as a linked table.
3.	Access will then use this table and the application proceeds from there.

Looking forward to hearing from you.

Regards,


----------



## Dragoen (Apr 10, 2009)

Mike,

I'm not sure what you mean by generating a random vector, and the 2 paths needed but I presume it is something that happens when you open the workbook. So if I understand correctly, what you want is to provide a means for the user to open the random vector workbook from within Access; have that workbook recalculate; then save the results (see step 2). Then continue in Access running other processes that reference the newly created workbook data. The random vector calculation can be controlled by an Autorun function, which can also save a copy of the workbook to a specified location that Access links to.

For step 2, I would leave the data in a worksheet, even if you save it off to a static path\filename. Then you can just keep it as a linked table in Access.

Step 3 works as planned.

There are plenty of sites with examples of Excel VBA code, especially Autorun functions. Hopefully Glas or Robert will offer some help too. I'll put together an example of a File Open dialog box for allowing the user to select a path and filename and how you feed it to a macro.


----------



## michael_j_allen (Sep 28, 2009)

Hi again, Dragoen,

Thanks for your reply. You seem to be on track yet to complete your understanding I am attaching a copy of the spreadsheet in question so you can see exactly what I mean. If you "play" with it you will notice that the numbers in the column, "Random key", change each time the spreadsheet is opened.

By way of background, these are record keys to a population file for extracting a sample frame for survey purposes. For obvious reasons, we would wish to change the contents of the sample frame for each succeeding survey while keeping the size of the sample frame constant.

If therefore I can organize a button to open the spreadsheet and a button to save it as a tab delimited file, I would be very happy indeed.

As regards the 2 paths, one path is the location of "excel.exe" and the other is the location of the spreadsheet itself, usually the current directory.

I am glad for help from whatever quarter so my best regards to Glas and Robert. May I add that I tend to find going through the Microsoft help more often than not, somewhat not specific to my immediate needs and therefore somewhat confusing. So any help is appreciated.

With best regards,

Mike


----------



## michael_j_allen (Sep 28, 2009)

Sorry I forgot the attachment


----------



## michael_j_allen (Sep 28, 2009)

Sorry,

seeme like .xls was not an upload option, so I created a .zip and uploaded it.


----------



## Dragoen (Apr 10, 2009)

Mike,

Sorry I won't be able to get any examples to you till early next week. I got swamped at work and will be away this weekend.


----------



## Dragoen (Apr 10, 2009)

Mike,

Here is one part of what you want. It's a way to browse for a file via an Access form and capture the Path & Filename. Once the textbox is filled in you can refernce the textbox value with "=Forms!Form1!txtFilename" to pass the Path\Filename to a macro or other function. I'm not sure what the best approach will be to open the spreadsheet to satisfy your needs. You can modify the Common Dialog filter settings to look for a specific filename too. It sounds like you need to call Excel and pass the filename as a Fileopen parameter to open the workbook and create the vector. If Excel is located in the same path on all PC's then that could be a static parameter.

Let me know if you agree.


----------



## michael_j_allen (Sep 28, 2009)

Thanks Dragoen,

I have used the Form you sent and have been able to extract the following two longish path strings:-

D:\Documents and Settings\Juliet\Desktop\TEMPDB\PLE2011\SAMPLE FRAME ROOT.xls 

D:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE

From all appearances, the path string for the Excel spreadsheet will definitely vary from machine to machine. Most of it however is the current directory of the MDB file issuing the request for the path. So if we have a way of saying "current directory” + "Excel file name” that should work. Is there anything from batch file language that might be useful here?

Other than that, the path for the EXCEL.EXE application is in Microsoft office which may be standard. Here we are working with XP, VISTA, and Windows 7 as the various platforms with OFFICE 2003 - 2010 as variants.

It would be nice to set it up, forget it, and not have it as an implementation issue.

I take your point about the parameters in the function call.

I am going to try using these two paths to open excel with the spreadsheet and see if I get the desired effect.

Regards


----------

