1

This is gonna be pretty specific.

I have an excel sheet with patient names and info in each row. The first row has the labels for each column. For instance, column a is PatientName. Im trying to export each row as their own word document with each cell in the row having its own line with a space/break between each. But I also want the label from row 1 to be with each specific row. Also make the first column aka PatientName the name for each document.

Ex.

Document Name: John Doe

Encounter Date
11-12-13

CC
Abdominal Pain

HPI
Mr. Doe is blah blah, and bunch of text

\Ex

Row #1 looks like: PatientName/EncounterDate/CC/HPI

Row#2 which is where we wanna start looks like: John Doe/11-12-13/Abdominal Pain/Mr. Doe blah blah

Each row has 27 cells

Thanks, and let me know if you need anymore info. VBA code.

Edit: This was the code I was using, but It makes each file into an Excel sheet and doesn't add row 1 with each row.

Sub SaveRowsAsCSV()

Dim wb As Excel.Workbook, wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim r As Long, c As Long

    Set wsSource = ThisWorkbook.Worksheets("AmazingChartsEncounters")

    Application.DisplayAlerts = False 'will overwrite existing files without asking

    r = 1
    Do Until Len(Trim(wsSource.Cells(r, 1).Value)) = 0
        ThisWorkbook.Worksheets.Add ThisWorkbook.Worksheets(1)
        Set wsTemp = ThisWorkbook.Worksheets(1)

        For c = 2 To 27 'I didn't test it when I changed the 7 here to 27
            wsTemp.Cells((c - 1) * 2 - 1, 1).Value = wsSource.Cells(r, c).Value
        Next c

        wsTemp.Move
        Set wbNew = ActiveWorkbook
        Set wsTemp = wbNew.Worksheets(1)
        'wbNew.SaveAs wsSource.Cells(r, 1).Value & ".csv", xlCSV 'old way
        wbNew.SaveAs "textfile" & r & ".csv", xlCSV 'new way
        'you can try other file formats listed at http://msdn.microsoft.com/en-us/library/office/aa194915(v=office.10).aspx
        wbNew.Close
        ThisWorkbook.Activate
        r = r + 1
    Loop

    Application.DisplayAlerts = True

End Sub
Maarten van Stam
  • 1,901
  • 1
  • 11
  • 16
rlawson
  • 21
  • 1
  • 3
  • I edited my post to put the code I was using. – rlawson Nov 15 '16 at 17:44
  • You could try looking into mail merge for this: see here for how to do it in VBA: http://stackoverflow.com/questions/1357121/executing-word-mail-merge and here on how to set up the initial merge: https://support.office.com/en-us/article/Mail-merge-using-an-Excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3 – OpiesDad Nov 15 '16 at 18:29
  • This shows some code to actually make a Word doc: http://stackoverflow.com/questions/23865854/export-from-excel-to-a-word-document-and-insert-a-header-in-word or here: http://www.exceltip.com/applications-word-outlook-in-vba/copy-worksheet-information-to-word-using-vba-in-microsoft-excel.html – OpiesDad Nov 15 '16 at 18:32
  • Would be easier for people to understand, if you provided screenshots with how it looks like and the end result – Niclas Nov 15 '16 at 20:52

0 Answers0