TransferText to Table

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295
Hi,

I'm trying to make a macro which imports a .txt file to a table.
I have created an Import Specification so it is imported a pre-defined way, but I can't seem to figure out how to created an extra column with an Auto-numbered Primairy Key in my Macro.

Can somebody help me, please?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
The autonumber field would not be set up / populated in the macro, rather you would do this in the table design (i.e. add the autonumber field field to the table) and not append any values to that field because it will populate itself.
HTH, Andrew. :)
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
In your first post you mentioned this :
I'm trying to make a macro which imports a .txt file to a table

Have you set up the table yet? If so, what fields does it have? If it doesn't have an autonumber field then add an autonumber field to the table. Every time you add a record to the table through your macro then the autonumber will populate itself - unless I'm misunderstanding your question I thought that was automatic.
 

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295

ADVERTISEMENT

Ah, yes I'm sorry.
Let me explain a bit more then.

I have a txt file which has to be imported in a table, but before importing the table must be emptied. (sort of refresh data)
I tried linking the file, but that way you can't add a column at all.
So I'm deleting the table before importing/creating the new one.

Maybe there is a way of deleting only the data so the stucture remains in tact and then import the txt file.

(edit little typo)
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

Yes you can clear out the just the data and leave the structure intact. This will also ensure that you use the same table each time you do an import which means you won't have to change your macro each time you run it.

Create a new query that you can run as the first step in your macro (i.e. it flushes out the table prior to the "import"). Create a new query -> add the table you want to clear -> Click Query -> Delete -> Select all records from table (using the *), Save.

Add it to your macro and that should do it. You might want to turn off the setwarnings at the start of the macro and then turn them back on before the macro stops - this will stop the Access messages confirming the record delete etc.

Andrew. :)

[Late Edit] P.S. you might want to change your existing make table query into an append query.
 

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295

ADVERTISEMENT

Thanx, that works great. :biggrin:

But now the next obstacle appears..... :(
Access remembers the numbers you deleted and everything wich is added is numbered where it ended the prevouis time.

i.e.:
1 - 100 after deleting and importing will be
101 - 200 after deleting and importing will be
201 - 300 etc...

Is it possible alway to start with 1,2,3....?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
How are you actually importing the data?

Can't you just copy the existing table then paste it and select structure only?
 

DaLiMan

Active Member
Joined
Jun 1, 2004
Messages
295
Yes, there are different solutions possible.
But not the way I want it to be.

I want it to be an automated task, with no manual interfering.
(Well just maybe to click a button.)

But I created a script in another program, since I couldn't get Access bend on it's knees to my wishes.... :)

Thanx for all the help though guys.
Did learn a few things again..... :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

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
Top