Re-using Saved Imports from a button on a form

M

M Skabialka

I used the wizard in Access 2007 to import a spreadsheet into a new table.
From there I run code to manipulate the data and clean it up to enter into
another table.
After the import I was asked if I wanted to save the steps, I said yes.
On the Ribbon bar, this is stored under Saved Imports. I would like to
automate this import process.

How do I set up a button to run the import?
How do I edit the saved import if it isn't exactly what I want?
How do I extract the code in the saved import so that I can use it for
several different imported spreadsheets of the same structure, and allow the
users to browse to select the next spreadsheet to import?
 
G

Gina Whipp

M Skabialka,

1.
Private Sub YourCommandButton_Click()
DoCmd.TransferSpreadsheet acImport, SpreadsheetType, "YourTableName",
"YourFileName", False
End Sub

2. http://www.regina-whipp.com/index_files/ImportExport.htm

3. Extract the Code??? Just use the same Import Specification. To allow
the users to select the file you can use...

http://www.mvps.org/access/api/api0001.htm

attached to a button on a form and on the same form put a button and use the
line below....

DoCmd.TransferSpreadsheet acImport, SpreadsheetType, "YourTableName", " &
Forms![YourForm]![YourFieldName] &", False

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
M

M Skabialka

Using your references (thanks) I have been able to import a user selected
table into Access, but it is converting text fields that look like numbers
into number fields. e.g. Column one contains 001, 001a, 001b in the first
three rows but Access shows 1 in each case.

In previous versions of Access I was able to create file specifications so
that I could designate import columns as text, etc. I have the table fields
set up as text but they are still not importing that way. How is that done
in Access 2007?

Mich

Gina Whipp said:
M Skabialka,

1.
Private Sub YourCommandButton_Click()
DoCmd.TransferSpreadsheet acImport, SpreadsheetType, "YourTableName",
"YourFileName", False
End Sub

2. http://www.regina-whipp.com/index_files/ImportExport.htm

3. Extract the Code??? Just use the same Import Specification. To allow
the users to select the file you can use...

http://www.mvps.org/access/api/api0001.htm

attached to a button on a form and on the same form put a button and use
the line below....

DoCmd.TransferSpreadsheet acImport, SpreadsheetType, "YourTableName", " &
Forms![YourForm]![YourFieldName] &", False

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

M Skabialka said:
I used the wizard in Access 2007 to import a spreadsheet into a new table.
From there I run code to manipulate the data and clean it up to enter into
another table.
After the import I was asked if I wanted to save the steps, I said yes.
On the Ribbon bar, this is stored under Saved Imports. I would like to
automate this import process.

How do I set up a button to run the import?
How do I edit the saved import if it isn't exactly what I want?
How do I extract the code in the saved import so that I can use it for
several different imported spreadsheets of the same structure, and allow
the users to browse to select the next spreadsheet to import?
Click to expand...
Click to expand...
 
G

Gina Whipp

Mich,

You can edit the specification. Go down to the bottom of my page and you
see the tab for Import/Export and there is a button to edit. You can edit
the field Data Type from there.

You're welcome!
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

M Skabialka said:
Using your references (thanks) I have been able to import a user selected
table into Access, but it is converting text fields that look like numbers
into number fields. e.g. Column one contains 001, 001a, 001b in the first
three rows but Access shows 1 in each case.

In previous versions of Access I was able to create file specifications so
that I could designate import columns as text, etc. I have the table
fields set up as text but they are still not importing that way. How is
that done in Access 2007?

Mich

Gina Whipp said:
M Skabialka,

1.
Private Sub YourCommandButton_Click()
DoCmd.TransferSpreadsheet acImport, SpreadsheetType, "YourTableName",
"YourFileName", False
End Sub

2. http://www.regina-whipp.com/index_files/ImportExport.htm

3. Extract the Code??? Just use the same Import Specification. To
allow the users to select the file you can use...

http://www.mvps.org/access/api/api0001.htm

attached to a button on a form and on the same form put a button and use
the line below....

DoCmd.TransferSpreadsheet acImport, SpreadsheetType, "YourTableName", " &
Forms![YourForm]![YourFieldName] &", False

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

M Skabialka said:
I used the wizard in Access 2007 to import a spreadsheet into a new
table. From there I run code to manipulate the data and clean it up to
enter into another table.
After the import I was asked if I wanted to save the steps, I said yes.
On the Ribbon bar, this is stored under Saved Imports. I would like to
automate this import process.

How do I set up a button to run the import?
How do I edit the saved import if it isn't exactly what I want?
How do I extract the code in the saved import so that I can use it for
several different imported spreadsheets of the same structure, and allow
the users to browse to select the next spreadsheet to import?
Click to expand...
Click to expand...
Click to expand...
 
M

M Skabialka

All I seem to be able to change is the name of the Saved import file, not
the specifications. What am I missing here?
Mich
 
M

M Skabialka

Make that the Description of the file that I can change, not the name of the
specification: e.g. "This is my import file".
Mich
 
G

Gina Whipp

Oops... BIG OOPS... you have to set up the Data Types while doing the
initial set-up. So what happens is go thru the step manually, click
Advanced and set your Data Types from the Window and then save that. Use
the same name as the previous Saved Import and now you should have no
problem.

Hmmm, going to have to update my Tips page!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
M

M Skabialka

Saving the Import File spec isn't going to work because the Excel file name
changes every time, and there are multiple files. The spec assumes the same
name.

So I am going back to code.
However, even though I have defined Column 1 as text in the temp table, as
it will contain "001", "001A", "001B" etc, and deleted all records (new
data), and inserted one row in with 'A' in text fields and 0 in number
fields, as soon as I use the following code to import from Excel, it changes
all of Column 1 to number, truncating text, so I get 1, 1, 1 instead of
"001", "001A", "001B" in the first three rows. blnHasFieldNames = true.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, Range:="Sheet1!"

Is there a third method? Copy and paste is not an option as I am trying to
make this transparent to the user.

Mich
 
M

M Skabialka

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, Range:="Sheet1!"

When this VBA code runs, it creates an error table, called
Sheet1$_ImportErrors, with sample data:

Sheet1$_ImportErrors Error Field Row
Type Conversion Failure LINE # 2
Type Conversion Failure QTY 2
Type Conversion Failure LINE # 3
Type Conversion Failure QTY 3
Type Conversion Failure LINE # 4
Type Conversion Failure QTY 4
Type Conversion Failure LINE # 5


Is there any way to intercept this and tell it how to handle the data until
it gets the hang of it?

Mich
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Security in Access 2013 1
Dynamic Naming of Form Button 3
Creating a Search using a combo box and then creating a report. 4
Import Wizard 3
Setting column width in a table with a macro is not working 0
How do I put saved import instructions into a switchboard 3
Getting Radio Buttons to work, that are inserted via AutoText 0
Looking for Information Word (Office 365) editing using VB.Net 1

Top