csv to Word populating

LuthersBoots · #1 06-25-2017, 12:06 PM

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 · #2 06-25-2017, 03:24 PM

.
Here is one way to accomplish the goal :

Code:

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 · #3 06-30-2017, 05:06 AM

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 · #4 06-30-2017, 09:15 AM

.
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 · #5 07-24-2017, 04:49 AM

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

Logit · #6 07-24-2017, 08:04 AM

Try this :

Code:

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 · #7 07-24-2017, 06:35 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Merge Field Not Populating	JennEx	Mail Merge	8	03-31-2016 06:30 PM
Populating fields from a table	serrix	Word	3	02-24-2016 12:19 AM
Populating a Word document with VBA\SQL	shabbaranks	Mail Merge	23	07-21-2015 01:31 PM
Reapting/Populating the same text in Word 2007	akwjaw14	Word	0	06-03-2014 03:40 PM
Need help populating dropdown box	antztaylor	Word	3	11-06-2012 05:46 PM