Excel Macro that loops within cells and writes to new file

Question

I need a macro to get some data from an Excel spreadsheet prior to importing it into MySql linking table.

There is a column of charity names and a column with a list of id's separated by commas (these represent charity types) To for example

Column A
CharityName1   
CharityName2
CharityName3
CharityName4

Column B
100, 101,104
 (empty)
104
100,105

I would like this to write a new csv file as follows

Thanks in advance for any help

you have basically the same problem as here: http://superuser.com/q/486294/160170 or here: http://stackoverflow.com/q/13126533/1370465 — Jook, Nov 19 '12 at 16:20

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

I would iterate through the second column and take the values from each cell into an array, lets call it mainArray. (This iterates rows and cols, be warned: How to iterate through a variable-column-length range in Excel using VBA)

Then I would parse until the delimiting ',' and store them in a an array called cellArray with the first value as the numbered cell they were taken from. Then, replace the original cell value in mainArray with the new cellArray. ( String-Manipulation: Split this String delimited by a - character? )

So cell B1 would become cellArray = { 1, 100, 101, 104 } which would be the first value in mainArray. Do this for each cell in column B for the used range.

Then I would create a new csv ( How to create a separate CSV file from VBA? ) and then input the data into it.

To input the data I would loop through each of my saved arrays and store as CellValue = array[0] + ", " + array[i]

Lastly, I would save my new CSV file.

edited May 23 '17 at 12:31

Community

1
1

answered Nov 19 '12 at 17:25

nicholeous

677
1
8
13

This is very high level guidance - closer to a comment than an answer – brettdj Nov 20 '12 at 01:03
Thanks for that - I probably should have mentioned that my experience with Macros is very limited so I wouldn't really know to go about implementing this – Phil Barnett Nov 20 '12 at 08:29
No problemo. The links should explain the details of the coding if you're unsure. I've always preferred doing things the hard way, I guess, haha. – nicholeous Nov 22 '12 at 16:11

brettdj · Accepted Answer · 2012-11-20T12:38:57.340

0

This code will quickly create a csv file c:\temp\dump.csv with this format

[Updated to handle your format I note that you may have lost data as Excel has applied scientific notation to your fields. For now I have added an ugly workaround to pad out the 0's. Should B2 be a 30 digit field?]

enter image description here

Sub GetEm()
Dim x()
Dim lngCnt As Long
Dim lngCnt2 As Long
Dim lngE As Long
Dim objFSO As Object
Dim objTF As Object
Dim vArr
Dim vArrElem

Set objFSO = CreateObject("scripting.filesystemobject")
Set objTF = objFSO.createtextfile("c:\temp\dump.csv", 2)

x = Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp)))
For lngCnt = 1 To UBound(x)
    lngE = InStr(x(lngCnt), "E")
    If lngE > 0 Then
        x(lngCnt) = CStr(Replace(Replace(x(lngCnt), ".", vbNullString), "E+", vbNullString) & Application.Rept("0", Right$(x(lngCnt), 2) - lngE + 1))
    End If
    If Len(x(lngCnt)) > 0 Then
        If Len(x(lngCnt)) Mod 3 = 0 Then
            For lngCnt2 = 1 To Len(x(lngCnt)) Step 3
                objTF.writeline lngCnt & ",'" & Mid$(x(lngCnt), lngCnt2, 3)
            Next
        End If
    End If
Next
objTF.Close

End Sub

edited Nov 20 '12 at 12:38

answered Nov 20 '12 at 01:02

brettdj

54,857
16
114
177

Many thanks for that - when I run that I get: Run Time error '9' Subscript out of range - when I 'debug' this line: If Len(x(lngCnt)) > 0 Then - is highlighted – Phil Barnett Nov 20 '12 at 09:23
@PhilBarnett can you post a sample of your file anywhere? – brettdj Nov 20 '12 at 09:25
The macro ran ok (I had made a nube error), however it wasn't quite what i was after - the first row of the outputted file was 1102307207308110 - what I was after was 1,102 | 1,307 | 1,207 | 1,308 | 1,112 | (where '|' is a new row) - this a sample of the charities csv http://tinyurl.com/c92y9rh , this is the csv your macro outputted http://tinyurl.com/bmo66ua , and this a example of what i was after http://tinyurl.com/cspaxmc – Phil Barnett Nov 20 '12 at 11:38
@PhilBarnett thx for the files. Your worked example in your question indicates the numbers are separated by `,`. - which my first code relied on. But I see these `,` are actually only formatting and your data is a long string of numbers that needs to be cut into 3 characters lengths. I will update – brettdj Nov 20 '12 at 11:45
How would I filter the output to only write lines when the first character of the charity id = '2' so just 1,207 in the above example – Phil Barnett Nov 20 '12 at 14:14

Excel Macro that loops within cells and writes to new file

2 Answers2