Import from Excel using teh same settinsg every time?

[unc2plo]

I import the same 20 reports from Excel every day. Is there a way to have access alway choose the same settings? I always want the "first row contains column headinsg" box checked, and I always want "no primary key checked".

Any ideas?

Thanks,
David
unc2plo@att.net

 

[Corticus]

Hi,

Instead of importing the records, couldn't you just make linked tables to the spreadsheets? That way you woudln't have to import anything, your tables would update automatically as you entered data into their respective spreadsheets. Once set up, these tables could be formatted to have no foreign key and whatever settings you don't want to repeatedly enter.

HTH,
Corticus

 

[unc2plo]

I tried to link directly to the reports, but they are shared by more than one person. I am importing them as a snapshot at the same time every day. They change throughout they day, so I need to import them to have the data stay as a snapshot rather than an ongoing thing.

Does that make sense?

 

[Corticus]

Perfect sense,

Unfortunately, I'm not sure how to do that
I was hoping to find a workaround. I think to do what you want might be kind of tricky...I'm working on it though...despair not!


Corticus

 

[Anne Troy]

No working on it, Corsicus.

It's called an Import Specification File.

Check out Help:

About import/export specifications and Schema.ini files
An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file. An import/export specification is stored with the default name: Filename_ImportSpec or Filename_ExportSpec in the database that you import to or export from.

You create an import/export specification by using the Import Text Wizard or by using the Export Text Wizard. On the first screen of either wizard, click Advanced, define the specification, and then click Save As. Click the Specs button to select the import/export specification the next time you use either wizard.

Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.

Alternatively, you can use a Schema.ini file in a Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Windows Registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.

Then, you can create a macro that runs the import for ya. I'll leave that part to Sparticus.

er...I mean Corticus.

 

[DonkeyOte]

Importing data from Excel to Access is fairly straight forward using VB (and not the import wizard) - any reason why you can't code it? You would only need to set up your table(s) once and then ignore the headings from then on.

eg...(Query1 is incidentally a delete Query set up in Access)

Dim obj1 As AccessObject
Dim strPath As String
Dim strDBName As String

Set appaccess1 = New access.Application
strPath = "R:currentinfoPODatabase"
strFile = "FINANCE_DATA.mdb"
strDBName = strPath & strFile
appaccess1.OpenCurrentDatabase strDBName

DoCmd.OpenQuery ("Query1")

DoCmd.TransferSpreadsheet acImport, 8, "DATA", "R:currentFinanceTronConsolSage.xls", True, "A1:N25000"

ActiveWorkbook.Close SaveChanges = False


End Sub

 

[Corticus]

Nice!