Can an Access database update an excel file?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an .mdb that I want to connect to an .xls file. I figured out
a few ways to import the data from the .xsl, but can't figure out how to have
the data saved back in to the .xls file once I edit the information in a form
..

Is this possible? If so, will such a scenario work with multiple users
working in the form at the same time?

Thanks for the assist!
-Adam
 
I've done this successfully with a few Excel files. First, make sure you
have a query/table set in Access with the data fields you want in your Excel
spreadsheet. Then, open Excel to a blank document. From the data menu at
the top, choose "Get External Data" then "New Database Query". Highlight "MS
Access Database" in the window and then click the "OK" button at the right.
Another window will pop up to have you select the database to draw from.
Once you do this, yet another window will pop up for you to choose the table
or query you want to draw from. You can select the columns by highlighting
them and > to the next window. Proceed through the wizard. Once you do
this, all the data from Access will dump into Excel. There are some options
as to how often you want it to refresh, etc. Just play around with the
settings. Then, every time you open this spreadsheet, the data will refresh.
Hope this helps you some.
 
Thank you for the quick reply. It does help. However, I still have a
question. Given that I want the .mdb to update from data inputted in the
..xls as well, will this solution work or will I need to take additional steps?

For more background, we have two groups working on one project. The first
group inputs data in to the .xls and the second group will input additional
information via the .mdb.

However, both groups need the ability to view what the other group has done
in their respective interfaces. Also, we need to have the ability for both
groups to input data at the same time...

I just found this post, and am a bit discouraged:
http://www.microsoft.com/communitie...86d9&mid=064e1dba-cb5f-4076-bf07-357656cd11bb

Thoughts?

-Adam
 
It is possible to manipulate an Excel file using VBA so that you can update
tables from the Excel data, and write data back to the Excel sheets. I am
sorry but I do not know where the code is buried. It was a lot of work.. I
cannot remember all of the details. When I stumble across it I intend to put
it into the public domain, but that is not going to help you now.
 
I thought that this feature was disabled due to the microsoft copyright
infringement lawsuit..

some guy out of brazil or argentina had a patent on this; and I swear
to god that this functionality is now disabled

-Aaron
 
David said:
It is possible to manipulate an Excel file using VBA so that you can update
tables from the Excel data, and write data back to the Excel sheets.
Click to expand...

The OP can also use Jet SQL e.g.

UPDATE [Excel 8.0;DATABASE=C:\Tempo\db.xls;HDR=YES;].[Employees$]
SET EmployeeName = 'Jamie'
WHERE EmployeeID = 1;

Cannot read/write if the workbook is password-protected; cannot write
if the worksheet is protected. Cannot update cells that contain
formulas. Updates to a formula cell's precedents will not be reflected
in the formula's value until the worksheet is recalculated using the
Excel app. Data typing may be an issue - see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/.


Jamie.

--
 
Back
Top