excel link

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
I am linking to excel, I want to then fire a macro as a make table and index the maketable afterword. How could I do that. I built the link, I built the macro and wrote the vba to execute the macro from excel. I just need to tell the file to be indexed after it is made, how do I do that part?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
There is another way to do it.
Create your table once, then manually add the indexes.

Then instead of using a Make Table Query each time, use an Append Query to add your records to this established "shell" of a table that already has the indexes.

I do this often. I will often run the Append Query by a macro, where I will first add another step to the macro that deletes the old data out of my table before I add new records to it (if this is what you need to do). Then it simply runs with a single click of the button.
 
Upvote 0
What I want was to allow someone to dump an excel file of say 50000 records into an area, Then I was going to use ADO to sql it to another Spreadsheet.

Worker is doing tons of Vlookups from a whole bunch of huge files and PC is constantly having problems with it failing.

I already had the ADO stuff written for some SAP files I link to.

Is there something easier I could do for worker.
 
Last edited:
Upvote 0
So the file will be diiferent often. Could I set the index and then replace instead of making the table. Updating would not work as I do not want past records.

Thanks so much for feedback.
 
Upvote 0
How will the table be different? The data or the structure?
If just the data, it is not a problem.

I don't think you quite understood the process I am proposing. Essentially, you would be just be replacing the data each time, and not the table structure. So all your indexes would remain and there would be no need to add them every time. An Append Query adds records to an existing table.

So what I am proposing is this:
- Run a Make Table once to initially create the table
- Add the indexes to the table
- Create an Append Query from your linked table to add the records to the table you made.

These three steps only need to be run once, and then never again.
Then just create a two step macro that does the following:

1. Deletes the old data out of the table.
Do this with a RunSQL command, with code like this:
DELETE [TableName].* FROM [TableName]

2. Runs your Append Query (use the OpenQuery command)

Then all you need to do is click on this macro and it will delete all old data from your table and import the new data from your linked table.
 
Upvote 0
Wont append queery only change records that are in the new recordset. Or will it wipe out all the old ones that are not in new file? I do not want the old ones as they are comming from different groups. I just want twhat is in the new file each time.
 
Upvote 0
OK, looks like you might need to brush up on your action queries, as I think you are confusing what different action queries do:
MAKE TABLE QUERY - creates a new table
APPEND QUERY - adds NEW records to an existing table
UPDATE QUERY - updates existing records in an existing table

The DELETE SQL code is cleaning out the table so there are no records in it before we run the Append Query to add our new records to it.
 
Upvote 0
I do appologize as I am not access expert.
But I want to only have the records from my link in the file when I am done. Appending sounds like it will add all the records to the table and leave the old one. I do not want the old ones updated or there I only want weaht is in the file the person will link to.
I am really sorry for bothering you with this and I am testing to see how it turns out.
 
Upvote 0
Appending sounds like it will add all the records to the table and leave the old one.
As I said, you will be running a DELETE command to delete all the records out of the table before you add the new records into it. Maybe an example will clarify it.

Let's say you created a table called MyTable that you want to add these records from your Append Query to this table. The first time through, let's say it adds 100 records.

Now, the next time around, let's say your Append Query has 200 records to add.

If you create the Macro that I described that has two steps in it, it will do the follow:
1. Run the DELETE command to delete all the records currently in MyTable. So when this step completes, there will be zero records left in MyTable.
2. Run the Append Query. This will add the 200 records to MyTable.

So when all is said and done, all you will have is the 200 new records. You will NOT have any old records. The DELETE command is deleting all old records from your table before using the Append Query to add new records to it.

That is what you want, right?
 
Upvote 0
I missed the delete part. I am so sorry, As I said Access ain't my best thing. Many thanks for the recapitulation.
It helped.
And again many thanks.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top