Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
hello,
I did a small excel vba program that displays data from an access database. The access database is used as a data container only. The client is now not happy with the fact that he always has to take care of two files (the XLS and the MDB), he'd like to have everything in one file. Is there a way to have the whole database included in the excel workbook (e.g. as OLE object)? I tried the OLE thing, but I was not successful... using office 2003, by the way. thanks a lot! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
Have you thought about writing the macro in ACCESS rather than Excel? Is the
Customer not happy with the fact there arre two files, or is the problem with the data entry being in Access and the Output being in Excel? We need a little bit more explanation of what the customer requirements are to determine the best solution. I think just adding a Front End (macro in excel) for writing the data into Access is may be the right solution. "mono" wrote: hello, I did a small excel vba program that displays data from an access database. The access database is used as a data container only. The client is now not happy with the fact that he always has to take care of two files (the XLS and the MDB), he'd like to have everything in one file. Is there a way to have the whole database included in the excel workbook (e.g. as OLE object)? I tried the OLE thing, but I was not successful... using office 2003, by the way. thanks a lot! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
thanks for your reply!
the client is not happy with the fact that there are two files. And they want to use Excel to see the data, as they might want to do further calculations in Excel. The macro in Excel only GETs data from the Access database, so Access is only the data container, because there's too much data to have everything in an Excel Workbook. (And there's no need to write TO the Database, the database is "read-only"...) hope this makes it a bit clearer "Joel" wrote: Have you thought about writing the macro in ACCESS rather than Excel? Is the Customer not happy with the fact there arre two files, or is the problem with the data entry being in Access and the Output being in Excel? We need a little bit more explanation of what the customer requirements are to determine the best solution. I think just adding a Front End (macro in excel) for writing the data into Access is may be the right solution. "mono" wrote: hello, I did a small excel vba program that displays data from an access database. The access database is used as a data container only. The client is now not happy with the fact that he always has to take care of two files (the XLS and the MDB), he'd like to have everything in one file. Is there a way to have the whole database included in the excel workbook (e.g. as OLE object)? I tried the OLE thing, but I was not successful... using office 2003, by the way. thanks a lot! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
Don't you think the better solution is to keep the data in Access. You can
always use the menu option in Access to see the data in Excel? If the customer is not happy, then simply perform the export from Access to Excel once and give him the excel file. Don't tell the customer the source is really an Access file. Access Menu. Tool - Office Links - Analyze it with Microsoft Excel "mono" wrote: thanks for your reply! the client is not happy with the fact that there are two files. And they want to use Excel to see the data, as they might want to do further calculations in Excel. The macro in Excel only GETs data from the Access database, so Access is only the data container, because there's too much data to have everything in an Excel Workbook. (And there's no need to write TO the Database, the database is "read-only"...) hope this makes it a bit clearer "Joel" wrote: Have you thought about writing the macro in ACCESS rather than Excel? Is the Customer not happy with the fact there arre two files, or is the problem with the data entry being in Access and the Output being in Excel? We need a little bit more explanation of what the customer requirements are to determine the best solution. I think just adding a Front End (macro in excel) for writing the data into Access is may be the right solution. "mono" wrote: hello, I did a small excel vba program that displays data from an access database. The access database is used as a data container only. The client is now not happy with the fact that he always has to take care of two files (the XLS and the MDB), he'd like to have everything in one file. Is there a way to have the whole database included in the excel workbook (e.g. as OLE object)? I tried the OLE thing, but I was not successful... using office 2003, by the way. thanks a lot! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
ok, I'll try to explain it mo
the Excel consists of some drop downs which the client uses to dynamically get data from the Access database. This works fine, the only problem is that the access database is an external file. I'm looking for a way to keep the current functionality and layout but to kind of include the access database into the Excel file (just because the client is afraid that when e.g. travelling, he only copies the Excel file to the local disc but not the Access database) "Joel" wrote: Don't you think the better solution is to keep the data in Access. You can always use the menu option in Access to see the data in Excel? If the customer is not happy, then simply perform the export from Access to Excel once and give him the excel file. Don't tell the customer the source is really an Access file. Access Menu. Tool - Office Links - Analyze it with Microsoft Excel "mono" wrote: thanks for your reply! the client is not happy with the fact that there are two files. And they want to use Excel to see the data, as they might want to do further calculations in Excel. The macro in Excel only GETs data from the Access database, so Access is only the data container, because there's too much data to have everything in an Excel Workbook. (And there's no need to write TO the Database, the database is "read-only"...) hope this makes it a bit clearer "Joel" wrote: Have you thought about writing the macro in ACCESS rather than Excel? Is the Customer not happy with the fact there arre two files, or is the problem with the data entry being in Access and the Output being in Excel? We need a little bit more explanation of what the customer requirements are to determine the best solution. I think just adding a Front End (macro in excel) for writing the data into Access is may be the right solution. "mono" wrote: hello, I did a small excel vba program that displays data from an access database. The access database is used as a data container only. The client is now not happy with the fact that he always has to take care of two files (the XLS and the MDB), he'd like to have everything in one file. Is there a way to have the whole database included in the excel workbook (e.g. as OLE object)? I tried the OLE thing, but I was not successful... using office 2003, by the way. thanks a lot! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
Another suggestion. Store the database as Excel (not Access) in the
workbook. You can export the data from access into excel and then hide and protect the worksheet as the source data. Let the customer use a copy of this worksheet that he can format and make changes to. What you probably want is simply add the Access file to the excel worksheet by doing the following 1) Insert Menu - Object - Create From file Tab 2) browse for the file 3) Check the box Display as Iconj. I performed this operation while recording a macro and this is the code it produced the macro below, but it doesn't work. Need to figure out what the problem is with this recorded macro. ActiveSheet.OLEObjects.Add(Filename:="C:\TEMP\Part Log.mdb", Link:= _ False, DisplayAsIcon:=True, IconFileName:= _ """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"" /NOSTARTUP ""%1""" _ , IconIndex:=0, IconLabel:="C:\TEMP\Part Log.mdb").Select "mono" wrote: ok, I'll try to explain it mo the Excel consists of some drop downs which the client uses to dynamically get data from the Access database. This works fine, the only problem is that the access database is an external file. I'm looking for a way to keep the current functionality and layout but to kind of include the access database into the Excel file (just because the client is afraid that when e.g. travelling, he only copies the Excel file to the local disc but not the Access database) "Joel" wrote: Don't you think the better solution is to keep the data in Access. You can always use the menu option in Access to see the data in Excel? If the customer is not happy, then simply perform the export from Access to Excel once and give him the excel file. Don't tell the customer the source is really an Access file. Access Menu. Tool - Office Links - Analyze it with Microsoft Excel "mono" wrote: thanks for your reply! the client is not happy with the fact that there are two files. And they want to use Excel to see the data, as they might want to do further calculations in Excel. The macro in Excel only GETs data from the Access database, so Access is only the data container, because there's too much data to have everything in an Excel Workbook. (And there's no need to write TO the Database, the database is "read-only"...) hope this makes it a bit clearer "Joel" wrote: Have you thought about writing the macro in ACCESS rather than Excel? Is the Customer not happy with the fact there arre two files, or is the problem with the data entry being in Access and the Output being in Excel? We need a little bit more explanation of what the customer requirements are to determine the best solution. I think just adding a Front End (macro in excel) for writing the data into Access is may be the right solution. "mono" wrote: hello, I did a small excel vba program that displays data from an access database. The access database is used as a data container only. The client is now not happy with the fact that he always has to take care of two files (the XLS and the MDB), he'd like to have everything in one file. Is there a way to have the whole database included in the excel workbook (e.g. as OLE object)? I tried the OLE thing, but I was not successful... using office 2003, by the way. thanks a lot! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
we're getting closer now.
I thought about the "Insert Menu - Object - Create From file" solution too, but it didn't work. The reason why using access is that there's just too much data to have it stored in an Excel Worksheet (the size of the access database is about 35MB). I could manually add the MDB as an object, but I didn't find a way then to create the connection to it from within the Excel macro... "Joel" wrote: Another suggestion. Store the database as Excel (not Access) in the workbook. You can export the data from access into excel and then hide and protect the worksheet as the source data. Let the customer use a copy of this worksheet that he can format and make changes to. What you probably want is simply add the Access file to the excel worksheet by doing the following 1) Insert Menu - Object - Create From file Tab 2) browse for the file 3) Check the box Display as Iconj. I performed this operation while recording a macro and this is the code it produced the macro below, but it doesn't work. Need to figure out what the problem is with this recorded macro. ActiveSheet.OLEObjects.Add(Filename:="C:\TEMP\Part Log.mdb", Link:= _ False, DisplayAsIcon:=True, IconFileName:= _ """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"" /NOSTARTUP ""%1""" _ , IconIndex:=0, IconLabel:="C:\TEMP\Part Log.mdb").Select |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
include access database in excel workbook
It seems to me that the only solution is an Access solution and not an excel
solution. The Access file is too large for excel, but excel is not too large for Access. Create macros iin access to automatically produce the excel worksheet and format the excel spreadhseet with basic features like drop down list or autofilters. Maybe the customer needs some Command scripts to automatically copy both the excel and access files from his Office PC to his Lap Top. How often does the Access Database get updated? How does the customer update the spreadsheet after the Database get updated. You need to look at all the customer processes first, then look for a solution that solves all the customer problems. I know you have partially have done this work, but does the customer know all the options. Sit down and write a requirement list and do the comparison with all the options. This doesn't have to be fancy. Scribble a couple of notes on a piece of paper if necessary. Having everything writen makes it easier to make decisions. "mono" wrote: we're getting closer now. I thought about the "Insert Menu - Object - Create From file" solution too, but it didn't work. The reason why using access is that there's just too much data to have it stored in an Excel Worksheet (the size of the access database is about 35MB). I could manually add the MDB as an object, but I didn't find a way then to create the connection to it from within the Excel macro... "Joel" wrote: Another suggestion. Store the database as Excel (not Access) in the workbook. You can export the data from access into excel and then hide and protect the worksheet as the source data. Let the customer use a copy of this worksheet that he can format and make changes to. What you probably want is simply add the Access file to the excel worksheet by doing the following 1) Insert Menu - Object - Create From file Tab 2) browse for the file 3) Check the box Display as Iconj. I performed this operation while recording a macro and this is the code it produced the macro below, but it doesn't work. Need to figure out what the problem is with this recorded macro. ActiveSheet.OLEObjects.Add(Filename:="C:\TEMP\Part Log.mdb", Link:= _ False, DisplayAsIcon:=True, IconFileName:= _ """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"" /NOSTARTUP ""%1""" _ , IconIndex:=0, IconLabel:="C:\TEMP\Part Log.mdb").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
How do break the link from my excel workbook to a access database. | Excel Discussion (Misc queries) | |||
HELP: VBA Code to extract data from an SQL Server database and put it into Access Database | Excel Programming | |||
How to include data on the same sheet some in database some not | Excel Worksheet Functions | |||
VLOOKUP in another workbook/Access database | Excel Programming |