Question Importing excel spreadsheet into Access

[gdshih]

I have imported an Excel spreadsheet into Access before, but the column count was always set. For my current project, I need to import an Excel spreadsheet but the column count is determined by the user. There are only 2 columns in the Access table so if the spreadsheet has 4 columns then the Access record will be displayed as "info from column 2 & vbCrLf & info from column 3 & vbCrLf & info from column 4" etc.

This is the code I have used before, but I'm just not sure how to change it in order to do what I want.

Thanks in advance.

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] rCnt = 1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Range.Rows.Count
tmpName = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]""
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] rCnt = 1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'skip header row
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] myRecord [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] tripCommunications
AddBlock = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]""
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] cCnt = 1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Range.Columns.Count
Obj = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](Range.Cells(rCnt, cCnt), Excel.Range)
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] cCnt
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'import case number
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]myRecord.CaseNb = Obj.value
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 2
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'import caption
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]myRecord.Caption = Obj.value
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 3
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'import ind CRD#
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]myRecord.IndCRD = Obj.value
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 4
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'import ind name
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]myRecord.LastNm = Obj.value
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 5
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'import service date 
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]myRecord.ServiceDt = Obj.value
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]InsertRecord(myRecord)
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][/COLOR][/SIZE]

 

[cjard]

What you're talking about is a pivot operation.. Youre going to take X number of columns, over Y number of rows of info and turn it into X * Y number of rows and 2 columns


First, you need to get better access to your data; throw that old code away, and follow the Microsoft tutorials in the DW4 link of my signature. Start with the "Creating a Simple Data App" one
You can, once you understand that, link your program to your sheet and to your db at the same time (ConnectionStrings.com - Forgot that connection string? Get it here! will help)

Then you'll need a loop like:

Dim yourAccessDBTable as New YourDataSet.YourAccessDBTable

'for each row in the datatable that was filled from the spreadsheet
For Each spreadsheetRow as YourSSRow In yourDataSet.YourSS

  'for each column in the table
  For Each dc as DataColumn in yourDataSet.YourSS.Columns

     'add a row to the table that we'll save to access, the 2 columns in this table are the name and the value
     'so dc.Name gives us the column name and item(dc) gives us the value
    yourAccessDBTable.AddAccessDBRow(dc.Name, spreadsheetRow.Item(dc))
  Next dc
Next spreadsheetRow

'save to db
yourAccessDBTableAdapter.Update(yourAccessDBTable)