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?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows
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.
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
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:

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows

ADVERTISEMENT

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.
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows

ADVERTISEMENT

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.
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
Windows
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?
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,215
Messages
5,509,875
Members
408,758
Latest member
himanshuagarwal

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top