• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Access 2003 - import into a table from Excel

I have an Excel 2003 spreadsheet that has in colum E3 : E10 data that I want to auto import into a table. I have this code

        .TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "ImportData", FilePath, False, "Employee info!E3:E10"

but it puts in one column. I want it to go to one record in 8 different columns
0
Pdeters
Asked:
Pdeters
  • 4
  • 3
1 Solution
 
IrogSintaCommented:
Strange, I just tested your code and it puts it in separate columns.  I'm not sure why it wouldn't do so with you.  Maybe someone else here may have an idea.
0
 
PdetersAuthor Commented:
It puts it in 8 different records - I want it in one record
0
 
IrogSintaCommented:
Sorry, I misunderstood.  What would you use for the names of those colurnns?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
PdetersAuthor Commented:
Field1, Field2, Field3, etc. is fine
0
 
IrogSintaCommented:
Try this.  This assumes that the data will be imported as Text data type.  You would have to modify it a bit if the data type comes in as numeric:
    Dim rst As DAO.Recordset
    Dim i As Byte, b
    
    i = 1
    For i = 2 To dcount("*", "NameOfTable")
        DoCmd.RunSQL "Alter Table NameOfTable Add Field" & i & " Text(10)"
    Next
    
    Set rst = CurrentDb.OpenRecordset("NameOfTable")
    i = 1
    rst.MoveNext
    Do While Not rst.EOF
        i = i + 1
        DoCmd.RunSQL "Update NameOfTable Set Field" & i & "='" & rst.Fields(0) & "'"
        rst.DELETE
        rst.MoveNext
    Loop
    rst.CLOSE
    Set rst = Nothing

Open in new window

0
 
PdetersAuthor Commented:
Does this code come after this?
   .TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "ImportData", FilePath, False, "Employee info!E3:E10"
0
 
IrogSintaCommented:
Yes the code should be run after the import.
0
 
Jeffrey CoachmanCommented:
It is also always helpful if you post a sample of the spreadsheet and the database.
Your sheet (or your table) may not be designed for this to work directly.

For example:
Are the actual field names in Row 1 only?
Any hidden rows or columns?
Any blank rows or columns?
...etc
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now