Renaming Imported File Automatically

tom_crisp

Board Regular
Joined
Jul 28, 2006
Messages
133
Hi guys, thanks for all the help recently!

I have a button on a form that imports a file, and another that runs a load of queries on a named table.

What I want to be able to do is automatically rename the file that is imported to 'tbl_full' so that the queries then have something to work on.

The problem is that the file imported will not always have the same filename. However the first two letters will be the same.

Perhaps there is a way to use sql 'LIKE' or similar?
---
OR
----
This needs to go into the onclick command for a button.. eg:
bold = what i want, italic = what i have.

Private Sub cmdImport_Click()
dim name as string
On Error Resume Next
'open import file dialog box
DoCmd.RunCommand acCmdImport = name
table with name = name becomes tbl_full

End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why not just import to an existing table?
 

tom_crisp

Board Regular
Joined
Jul 28, 2006
Messages
133
This system will be used many times, so I dont know if it will work.

Do you mean that I should keep the skeleton of the table there all the time, and just delete the data from it when one run through is complete?

I shall try this and see what happens.

EDIT:

This is an OK solution, just requires me to add a bit of guidance for the users.

Thanks for your help. :biggrin:
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
Considering you already have something working, you probably shouldn't bother with this suggestion.

One of my preferred techniques is to store information in tables related to the current status of any application. This is a bit vague so I'll explain.

Lets say you import some data and it comes in as tableX.
You'd then go to a table and save the name tableX.
Your application would then use a SQL statement to extract that table to use elsewhere in the application, including dynamically generating new queries that use that new tableX name.

Where this gets interesting is, lets say you import a new table each day with a different unique name. You could use one field that has something like "imported_table" in it and then the next is the name tableY. There could be other entires for tableW, tableZ and tableB. You could use a SQL statement to extract a list of those tables.

One use of something like this is if you were to build a combo or listbox in a form, you could generate a current list of all imported tables. The same form could have your button on it - you'd tell your form that when that button is clicked, please run your queries on the selected table (in the list or combo box).

Take it a step further. In another record, store the value "last_selected" and then the name of the table selected. When the form is first opened, have it go get that last_selected value and then have the form select it. ie, you're now carrying forward information done the last time it was opened to a new instance.

This could easily be adapted to an automated import process that just happens to re-assign that last_selected value.

Take it another step further - adaptable default tables. Use at least a 3 table one-to-many relationship with the top table have an index, a field with "table_list" and then it refers to a 2nd table that holdeds an index & then one record per unique table. A third table could hold user defined properties of that table (in another one-to-many relationship) such as details that describe it. What days it covers, it's purpose, whatever you need.

What I'm really getting at is - there probably is no way you need something this complex for something that pretty much already works. However, there is a reason why some complex suggestions are made here - and it's things like this. Something very simple like storing a single detail for use in an application can be carried forward into something much more complex and versatile. I'll add that just about everything I've written in the last few years uses some variant of this technique.

Mike
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

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