Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA File Import, Error 2391

Posted on 2013-05-14
5
Medium Priority
?
1,593 Views
Last Modified: 2013-05-14
Receiveing error 2391 - Field Store Name: does not exist in destination table tmpShipping.  The field is not in the table or the spreadsheet not sure how to troubleshoot.

Private Sub Command3_Click()
Dim strFile As String 'Filename
Dim strFileList() As String  'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String

DoCmd.SetWarnings False
path = "G:\Store Planning\Projects\Parts Tracker\ToImport\"

'Loop through the folder & build file list

strFile = Dir(path & "*.xls")

While strFile <> ""

'add files to the list

intFile = intFile + 1

ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()

Wend

'see if any files were found

If intFile = 0 Then

MsgBox "No files found"

End If

'cycle through the list of files

For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpShipping", filename, True 'Erroring on this line

Next intFile

DoCmd.SetWarnings True

End Sub

Open in new window

0
Comment
Question by:jmac001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39165685
I'm guessing your table 'tmpShipping' has a field called 'Store Name', but your spreadsheet does not have a matching header called 'Store Name'. I would start by checking those two things.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39165746
actually, the error codes states the reverse.

you don't have field named "Store Name" in your table tblShipping.

Open your table tblShipping in design view and check the names of the fields,

you might have a field for "store Name", but check  the spelling, space or underscore.

if you don't have the field, add it to your table.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39165769
hmm looking at your code, you are missing a part

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpShipping", filename, True 'Erroring on this line


it should be

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpShipping", filename, True, "TheNameOfSheetGoeshere"

or if the name of the sheet is "Sheet1"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpShipping", filename, True, "Sheet1!"

note*- if you have multiple sheets in your excel file you should specify the sheet, but if have only one sheet then no need to.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39166049
Try importing the spreadsheet manually, and look at the resulting table to see what the field names and data types are.  It might be best to first import the Excel data into a new table, and then run an append query to add the data to the regular table, converting data types as needed, since sometimes Excel values are not imported as the right data types for Access tables.
0
 

Author Comment

by:jmac001
ID: 39166173
Thanks capricorn1 adding the sheetname and cleaning up the file fixed the issue and it now importing to the temp table.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question