Okay, now, be aware I have no way to test this, so consider it "on the fly
air code", but it should give you and idea of how it works. You did not say
what the other columns, but for example purposes, I will call the fields
[multiD] and [multiY]
Dim rstXL as Recordset
Dim rstNew as Recordset
Dim dbf As Database
Dim aryD() as string
Dim aryY() as string
Dim intD as Integer
Dim intY as Integer
set dbf = CurrentDb
set rstXL = dbf.OpenRecordset("YourXLTable")
set rstNew = dbf.OpenRecordset("NewTableName")
If rstXL.Recordcount = 0 Then
MsgBox "No data to Process"
rstXL.Close
rstNew.Close
Set rstXL = Nothing
Set rstNew = Nothing
Set dbf = Nothing
Exit Sub
End If
Do Until rstXL.EOF
aryD = Split(Nz(rstXL![multiD],""), ",")
aryD = Split(Nz(rstXL![multiD],""), ",")
For intD = 0 To Ubound(aryD)
For intY = 0 To Ubound(aryY)
With rstNew
.AddNew
![Dfield] = aryD(intD)
![Yfield] = aryY(intY)
![AnotherField] = rstXL![AnotherField]
...
![LastField = rstXL![LastField]
.Update
End With
Next intY
Next intD
rstXL.MoveNext
Loop
rstXL.Close
rstNew.Close
Set rstXL = Nothing
Set rstNew = Nothing
Set dbf = Nothing
Gina said:
Correct. The original record with the 5 values would not be saved, but there
would be 5 rows of data, in each row of data the value from column D would be
unique, and the values from columns a-c and e-x would be populated from the
original record.
There are 2 columns in the spreadsheet that have the potential for multiple
values.
--
Thanks, Gina
:
Is it just column D?
Lets assume column D has the values in your previous example. The routine
would then create 5 records in the new table, one with each of the vaules.
The original record with all 5 values would not be replicated in the new
table. Is this correct?
:
The data in the field can vary from 1 value to 16 values. An example of the
type of data in the field could be:
01, 05, 32, 31, 99
The table has text in columns a-x. This information above, could be in
column D.
A new duplicate row would need to be created for each unique value in column
D, replicating the values in columns a-x in the newly created row.
Does that help? or have I confused the issue ....
I appreciate the help!
--
Thanks, Gina
:
Are there always the same number of items between the commas, or can they vary?
Are the text or numeric?
Post a couple of examples, please.
:
Is there any information you can provide me with that could split the field
create the duplicate line entries?
--
Thanks, Gina
:
Not during the transfer itself; however, you could import the table as is,
then write code to populate the fields correctly.
:
I am trying to import an excel spreadsheet, but have run into a data issue.
One of the columns I am trying to import containes multiple values, separated
by a comma. For each value in this column, I need to create a new row during
import, replicating all of the values from the other columns in the
spreadsheet.
Is there a way to do this systematically?
