csv to Word populating

[LuthersBoots]

I have a number of fields from a database that I plan on exporting into a csv on a regular basis. I would then like the different fields to be populated into a word document (or similar) in a particular style.



eg The Sheet would look like this, with the column headings in bold. Under each heading is a series of names/codes (probably about 30 or so under each header.

FIELD 1 FIELD 2 FIELD 3 FIELD 4
Test1 TestA Test& Test^
Test3 Test( Test* TestT

I would then like to populate a document that looks like below. With the idea being that this information could then be cut and pasted into an email. So I deally I would like that to be in a Word document or similar.



Test1
TestA
Test&
Test^


Test3
Test(
Test*
TestT


Can anyone advise on the best way to acheive this and make it as easy as possible so this can be done quickly on a weekly basis.

LB

[Logit]

.
Here is one way to accomplish the goal :

Option Explicit

Sub Macro1()
Dim Pos As Integer
Dim LC As Variant
Pos = 1

Application.ScreenUpdating = False

MainLoop:

LC = Cells(Pos, Columns.Count).End(xlToLeft).Column
Range(Pos + 1 & ":" & Pos + LC - 1).Insert Shift:=xlDown
Range(Cells(Pos, 2), Cells(Pos, LC)).Copy
Cells(Pos + 1, 1).PasteSpecial Transpose:=True
Range(Cells(Pos, 2), Cells(Pos, LC)).Clear
Pos = Pos + LC
If Cells(Pos, 1) <> "" Then GoTo MainLoop

Range("A2:A4").EntireRow.Delete
Range("A1").Value = "Sorted"
Columns(1).AutoFit

Application.ScreenUpdating = True

InsertBlankRows

End Sub

''/// For a set range of rows in a column
Sub InsertBlankRows()
'Step1:  Declare your variables.
    Dim MyRange As Range
    Dim iCounter As Long
    
Application.ScreenUpdating = False
    
'Step 2:  Define the target Range.
    Set MyRange = Range("A2:A50")
    
'Step 3:  Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 1 Step -4
    
'Step 4: Insert two blank rows.
    MyRange.Rows(iCounter).EntireRow.Insert

'Step 5: Increment the counter down
    Next iCounter
    
Application.ScreenUpdating = True

End Sub

[LuthersBoots]

Thanks very much for this. I am sorry, how do I action this script on the existing spreadsheet? I am using Excel for OSX.

[Logit]

.
I've not worked with the Mac version of Excel but presume it functions the same as Windows version.

Paste the macro code into a Routine Module. Place a command button on the spreadsheet attached to the macro code.

[LuthersBoots]

Hi there, have you set this one up to do just 4 columns? The information I actually want is spread amongst 9 columns

[Logit]

Try this :

Option Explicit


Sub Macro1()
Dim Pos As Integer
Dim LC As Variant
Pos = 1

Application.ScreenUpdating = False

MainLoop:

LC = Cells(Pos, Columns.Count).End(xlToLeft).Column
Range(Pos + 1 & ":" & Pos + LC - 1).Insert Shift:=xlDown
Range(Cells(Pos, 2), Cells(Pos, LC)).Copy
Cells(Pos + 1, 1).PasteSpecial Transpose:=True
Range(Cells(Pos, 2), Cells(Pos, LC)).Clear
Pos = Pos + LC
If Cells(Pos, 1) <> "" Then GoTo MainLoop

Range("A2:A6").EntireRow.Delete
Range("A1").Value = "Sorted"
Columns(1).AutoFit

Application.ScreenUpdating = True

InsertBlankRows
Range("A2:A4").EntireRow.Delete
Range("A1").Select

End Sub
''/// For a set range of rows in a column
Sub InsertBlankRows()
'Step1:  Declare your variables.
    Dim MyRange As Range
    Dim iCounter As Long
    
Application.ScreenUpdating = False
    
'Step 2:  Define the target Range.
    Set MyRange = Range("A2:A50")
    
'Step 3:  Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 1 Step -9
    
'Step 4: Insert two blank rows.
    MyRange.Rows(iCounter).EntireRow.Insert

'Step 5: Increment the counter down
    Next iCounter
    
Application.ScreenUpdating = True

End Sub

[macropod]

Is there a reason for not using mailmerge, direct from your 'database'? There seems to be no need for either an intermediate CSV file or a macro for the processing.