How to build a dialogue box that chooses a different Access data source for Excel.
Experts,
I have several Access data sources with different data.
The names of the Access databases are different
The table structure is identical in all cases.
The data is used in a simple Excel Pivot Table.
When I want to pull different data into the Pivot Table I have to open the data connection and modify the property settings.
This requires confirming all of the inputs.
I want to build a dialogue box that changes the Source database and leaves all of the other properties the same.
Can the connect string be inserted into a module that contains a break to insert the database name by means of a "file open" type dialogue?
Thanks People
AJP
I have several Access data sources with different data.
The names of the Access databases are different
The table structure is identical in all cases.
The data is used in a simple Excel Pivot Table.
When I want to pull different data into the Pivot Table I have to open the data connection and modify the property settings.
This requires confirming all of the inputs.
I want to build a dialogue box that changes the Source database and leaves all of the other properties the same.
Can the connect string be inserted into a module that contains a break to insert the database name by means of a "file open" type dialogue?
Thanks People
AJP
use the Application.FileDialog() object. The results of the user's selection will facilitate what actions your application takes -- which database is opened.
You could go at this the opposite way, have one access database with linked tables from all the other mdb's, and use some
vba object to open excel ad run the pivot table from a form in access, with a table selected.
e.g. access mdb with all linked tables
form in mdb with drop down of table names..
button to export the table contents to excel, open excel object, vba automating add new sheet with pivot table...
vba object to open excel ad run the pivot table from a form in access, with a table selected.
e.g. access mdb with all linked tables
form in mdb with drop down of table names..
button to export the table contents to excel, open excel object, vba automating add new sheet with pivot table...
ASKER
Still puzzling over this one :)
in the meantime, I rename the attached files with the same name and excel refreshes the data ok.
in the meantime, I rename the attached files with the same name and excel refreshes the data ok.
I've requested that this question be deleted for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
perhaps we could rephrase the initial question.
How do we build a dialoque box that allows you to choose a different database for the attached tables?
How do we build a dialoque box that allows you to choose a different database for the attached tables?
<I am sure a UserForm with a combo box could be created.>
You could post a sample Excel and Access file for us to work with
You could post a sample Excel and Access file for us to work with
ASKER
Nick67
thanks, i will put up an example within 24 or so
AJP
thanks, i will put up an example within 24 or so
AJP
ASKER
Nick67 et al,
All databases are from a previous version of Access and are identical in structure but have different names.
Currently, in order to change the underlying data, I make a copy of "LinkData2" and change the name to "LinkData".
As far as Excel is concerned, the data source for the excel pivot table remains the same and displays the result from the renamed database.
I want to leave the name of the source databases as they are and change the Data Connection Properties by means of a "File Open" type dialoque.
AJP LinkData.zip
All databases are from a previous version of Access and are identical in structure but have different names.
Currently, in order to change the underlying data, I make a copy of "LinkData2" and change the name to "LinkData".
As far as Excel is concerned, the data source for the excel pivot table remains the same and displays the result from the renamed database.
I want to leave the name of the source databases as they are and change the Data Connection Properties by means of a "File Open" type dialoque.
AJP LinkData.zip
ASKER
LinkData folder resides in C:\
Give this a spin and see how it works
DevLinkData.xlsm
DevLinkData.xlsm
ASKER
The code stops at :
ActiveWorkbook.PivotCaches
ActiveWorkbook.PivotCaches
It stops for me there too, because I run Office 2003, and not 2007+
Try this one and report there error number and text you may get
DevLinkData.xlsm
Try this one and report there error number and text you may get
DevLinkData.xlsm
ASKER
same result...
I am using Office Pro 10
possibly related to:
Provider=Microsoft.ACE.OLE
Also, ConString has different syntax, for Excel it is ConString, others use ConnString
I am using Office Pro 10
possibly related to:
Provider=Microsoft.ACE.OLE
Also, ConString has different syntax, for Excel it is ConString, others use ConnString
ASKER
Run=time error '1004':
Application-defined or object-defined error
Application-defined or object-defined error
Constring is a variable I declared earlier in the procedure, not an instrinsic VBA property. I'll try it in 2010 later this evening.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We are where I expected to be at this point.
The next step will no doubt raise another question.
Cheers
AJP
The next step will no doubt raise another question.
Cheers
AJP
On another sheet you could store MRU's for the db's you opened lately
And probably you could use a FileDialog for any new one.
But, if you are doing this in Excel, I think you are in the wrong primary Zone, as the only really 'Access' part of this is the connection string--which you already have worked out.
If you post an Excel file and the connection strings I'll see what I can do--but you may want to 'request attention' and change the primary Zone to Excel, to get an Excel Expert to give you a hand.