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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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