Last week I got very small project from one of my clients to create vba micro to process data row by row from one excel file brake one text column into records with 250 characters long and write output into new excel file. First I decided to use ADO (AxtiveX Data object) I wrote a code to open adodb connection and use recordset to process records in the file. But somehow data got corrupted in some of the records. I could not figure out why.
So I started thinking about alternative solution and come up with brilliant idea. I decided to use workbook object.
I created workbook and worksheet objects for each of the files.
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Next I promt client enter file location in data folder
Dim a As String
a = InputBox(“Please enter file name you want to process” & vbNewLine & “make sure file is located in c:/data/ folder”)
If Len(a) = 0 Then
MsgBox “Please start again and enter file name”
Exit Sub
End If
Then I opened both files.
Set wb = Workbooks.Open(“c:\data\” & a, True, True)
Set wb2 = Workbooks.Open(“c:\data\notes2.xlsx”, True, False)
Set ws = wb.Worksheets(1)
Set ws1 = wb2.Worksheets(1)
After that I determinate last row in input file
Dim lastrow As Long
lastrow = ws.Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
And now I looped thruout the records and chanked text column into multiple records
Dim l As Integer
Dim i As Integer
Dim y As Long
Dim r As Long
Dim dr As Integer
r = 1
y = 1
Do Until r > lastrow
If IsNull(ws.Cells(r, “g”).Value) Or ws.Cells(r, “g”).Value = Empty Then
l = 0
Else
l = Len(ws.Cells(r, “g”).Value)
End If
i = 1
dr = 1
If l = 0 Then
ws1.Cells(y, “a”).Value = ws.Cells(r, “a”).Value
ws1.Cells(y, “b”).Value = 1
ws1.Cells(y, “c”).Value = dr
ws1.Cells(y, “d”).Value = ws.Cells(r, “d”).Value
ws1.Cells(y, “e”).Value = ws.Cells(r, “e”).Value
ws1.Cells(y, “f”).Value = ws.Cells(r, “f”).Value
ws1.Cells(y, “g”).Value = “”
y = y + 1
Else
Do Until l < 1
ws1.Cells(y, “a”).Value = ws.Cells(r, “a”).Value
ws1.Cells(y, “b”).Value = 1
ws1.Cells(y, “c”).Value = dr
ws1.Cells(y, “d”).Value = ws.Cells(r, “d”).Value
ws1.Cells(y, “e”).Value = ws.Cells(r, “e”).Value
ws1.Cells(y, “f”).Value = ws.Cells(r, “f”).Value
If l < 250 Then
ws1.Cells(y, “g”).Value = Mid(ws.Cells(r, “g”).Value, i)
Else
ws1.Cells(y, “g”).Value = Mid(ws.Cells(r, “g”).Value, i, 250)
End If
l = l – 250
i = i + 250
dr = dr + 1
y = y + 1
Loop
End If
r = r + 1
DoEvents
Loop
Adn finally I closed all the objects, released memory and display message that process completed.
wb.Close False
wb2.Close True
MsgBox “Process Completed”
Set ws1 = Nothing
Set ws = Nothing
Set wb = Nothing
In my openion this is the easiest way to process data from one input file and produce output file. if anyone know others ways to do it please post. I would love to hear from you
