Converting single comma-separated row into multiple rows (Access 2010) again

Topic

New Reply

Please help. I posted a question over a year ago (listed below) and I am having a issue getting the response resolution provided to work.

I am having a problem when attempting to run the code. I receive the following error:

Run-time error ‘ – 2147352565 (8002000b)’:
The item with the specified name wasn’t found.

After clicking Debug, it highlights:
Sheets(zDestSht).Cells(1, 1).Value = “Company#”

Would you know how to resolve this problem?

Thanks!

Converting single comma-separated row into multiple rows (Access 2010)

[INDENT]If anyone could provide advice on how to solve my dilemma, that would be terrific.

I have a table in Access 2010 that contains a comma delimited list of zip codes in a single cell by company#. This list is provided to my database from a different system and the list is unable to be modified. I would like to create a new table with the list where the single comma separated rows are converted into multiple rows with 1 zip code for each company#.

Attached is this post is a sample of the data I am using. The table is called tblListingsAndZips.

I would like to keep my original table and create a new table with a single zip code listed for each company#.

Any help would be much appreciated!

[/INDENT]
[/COLOR]

kgudgel,

If this is what you’re after:

Then this code:
Code:
Option Explicit

Sub ProcessZips()

Dim vRawZips As Variant
Dim iZipCnt As Integer
Dim iCntr As Integer
Dim lDestRow As Integer
Dim zCCode As String
Dim zDestSht As String

Application.ScreenUpdating = False
[C2].Select
lDestRow = 2
zDestSht = “NewList”
Sheets(zDestSht).Cells(1, 1).Value = “Company#”
Sheets(zDestSht).Cells(1, 2).Value = “ZipCode”

Do
ActiveCell.Offset(1, 0).Select

vRawZips = Split(ActiveCell.Offset(0, -1).Value, “,”)
zCCode = ActiveCell.Value
iZipCnt = UBound(vRawZips) – 1 ‘*** Array zero based!

For iCntr = 0 To iZipCnt
Sheets(zDestSht).Cells(lDestRow, 1).Value = zCCode
Sheets(zDestSht).Cells(lDestRow, 2).Value = vRawZips(iCntr)
lDestRow = lDestRow + 1
Next iCntr

Loop Until ActiveCell.Value = “”

End Sub ‘Process Zips
Will accomplish the task in Excel and you can then import the table into Access. HTH :cheers:

Reply | Quote

Replies

You probably don’t have a sheet called “NewList”. Do you?

cheers, Paul

Reply | Quote

I suspect Paul has identified your problem, but some further information would be helpful in suggesting a long-term solution. Is this a one-time project, or do you need to do this periodically, and if so how frequently. If you need to do it every week, a better solution would be to create a procedure in your database that simply lets you click a button to create the updated version. On the other hand, if you do it once a year, the Excel approach RG gave you should work – if you have an Excel Workbook called NewList.xlsx.

Reply | Quote

To run it from Access, your code would be a lot simpler, something like:

set rs=currentdb.openrecordset(“SELECT CompanyID, ZipCode FROM tblCompany WHERE ZipCode Is Not Null”,dbopendynaset)

do while rs.eof = false
varzip = split(rs!ZipCode,”,”)
For N=0 to ubound(varzip)
currentdb.execute “INSERT INTO tblCompanyZip (CompanyID, Zip) VALUES (” & rs!CompanyID & “,” & varzip(n) & “)”
Next
rs.movenext
loop

Reply | Quote

MarkLiquorman – Thanks you for your help! This has significantly helped answer my questions.

Reply | Quote

Glad to help. Good luck with it.

Reply | Quote