macro to create text files

[dmj120]

I have a work project to copy/paste procedures from an excel file into a text file, after which our IT dept will upload those text files to our database.

Below is a small example. I have well over 200 'procedures' that each need to be saved as a text file (or pdf) and sent to our IT team.

Is it possible to have a macro:
1. find the unique procedures (a, b, c, etc.)
2. send cols B and C to a test or pdf file
3. have the file name as the "procedure name? (ie a, b, c, etc)
4. saving all to a specific folder (or even creating a folder) would be awesome!

As you can see, each procedure varies in tasks.

procedure	task​	description
a	1​	Confirm vessel and piping are free of leaks and loose connections. Repair as necessary. Record action taken in task notes.
a	2​	Test for chlorine. If above 0.06 ppm total chlorine, replace carbon filter.
b	1​	Check vessel and piping for leaks and loose connections. Repair as necessary. Record action taken in task notes.
b	2​	Run a cycle. Check for any abnormal conditions. Repair as necessary. Record action taken in task notes.
c	1​	Request permission from Equipment User to begin work. Document this notification in task notes.
c	2​	Contact qualified vendor and schedule preventative maintenance check.
c	3​	Perform energy isolation assessment. Record isolation number in task notes, or document that isolation was not required.
c	4​	Confirm proper oil level and refrigeration charge.
c	5​	Once per calendar year, take oil sample and have it tested for moisture content and acid level.
c	6​	Attach preventative vendor’s maintenance check list to PM.
c	7​	Grease pump motors with approved lubricant as applicable.
c	8​	Check the Abnormal Observation box on the work order, as necessary, for unusual or unexpected observations.
c	9​	Communicate to the Equipment User that work is complete. Document this notification in task notes.
d	1​	Confirm fan coil unit proper operation and verify unit is free of vibration.
d	2​	Ensure the fan coil unit for any buildup of dirt.
d	3​	Wash filters.
d	4​	Confirm thermostat proper operation.
d	5​	Ensure condenser is free of loose hardware or vibration.
d	6​	Verify fan proper operation.
d	7​	Confirm unit is free of leaks or external oil.
d	8​	Check that sight glass is full and indicates dry system.
d	9​	Verify all panels are securely tightened.
d	10​	Verify no excessive or abnormal noise present.

 

[DanteAmor]

4. saving all to a specific folder (or even creating a folder) would be awesome!

It is not clear how to create the folder, so change the specific folder on this line.

sPath = "c:\trabajo\pdfs\"

Try this:

Sub create_worksheets()
  Dim wb As Workbook, sh As Worksheet
  Dim c As Range, ky As Variant
  Dim sPath As String
 
  Application.ScreenUpdating = False
  sPath = "c:\trabajo\pdfs\"
 
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(3))
      .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 1, ky
      sh.Range("B:C").Copy
      Set wb = Workbooks.Add(xlWBATWorksheet)
      ActiveSheet.Paste
      wb.ExportAsFixedFormat xlTypePDF, sPath & "\" & ky & ".pdf"
      wb.Close False
    Next ky
  End With
  sh.Select
  sh.ShowAllData
  Application.ScreenUpdating = True
End Sub

 

[dmj120]

  Set sh = ActiveSheet

Thanks, I'll play around this weekend. Can I update "activesheet" to the sheet's name?
ie:

Set sh = Prod Tasks

 

[DanteAmor]

Can I update "activesheet" to the sheet's name?

Set sh = Sheets("Prod Tasks")

 

[dmj120]

This works great!! Is it possible to format column C to wrap -but- give a specific column width?

When I run this, column C wraps, but is only a "normal excel column width"

 

[DanteAmor]

Try this

Sub create_worksheets()
  Dim wb As Workbook, sh As Worksheet
  Dim c As Range, ky As Variant
  Dim sPath As String
  
  Application.ScreenUpdating = False
  sPath = "c:\trabajo\pdfs\"
  
  Set sh = Sheets("Prod Tasks")
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(3))
      .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 1, ky
      sh.AutoFilter.Range.Offset(, 1).Copy
      Set wb = Workbooks.Add(xlWBATWorksheet)
      ActiveSheet.Paste
      Columns("A:A").EntireColumn.AutoFit
      With Columns("B:B")
        .ColumnWidth = 40
        .WrapText = True
      End With
      wb.ExportAsFixedFormat xlTypePDF, sPath & "\" & ky & ".pdf"
      wb.Close False
    Next ky
  End With
  sh.Select
  sh.ShowAllData
  Application.ScreenUpdating = True
End Sub

 

[dmj120]

I must be doing something wrong. I ran the code but the column still prints to the pdf with the 'standard excel column width'. I tried increasing the width to 70, and still the same.

The excel file updates the column width correctly, but once printed to pdf, it reverts back.

 

[DanteAmor]

If excel is ok, try the following:

Sub create_worksheets()
  Dim wb As Workbook, sh As Worksheet
  Dim c As Range, ky As Variant
  Dim sPath As String, sTemp As String
  
  Application.ScreenUpdating = False
  sPath = "c:\trabajo\pdfs\"
  sTemp = "filetemp"
  
  Set sh = Sheets("Prod Tasks")
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(3))
      .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 1, ky
      sh.AutoFilter.Range.Offset(, 1).Copy
      Set wb = Workbooks.Add(xlWBATWorksheet)
      ActiveSheet.Paste
      Columns("A:A").EntireColumn.AutoFit
      With Columns("B:B")
        .ColumnWidth = 40
        .WrapText = True
      End With
      wb.SaveAs sPath & sTemp
      wb.ExportAsFixedFormat xlTypePDF, sPath & "\" & ky & ".pdf"
      wb.Close False
    Next ky
  End With
  sh.Select
  sh.ShowAllData
  Application.ScreenUpdating = True
End Sub

 

[dmj120]

Still gives me the same pdf output. I'm going to try more searching, I found a couple articles about print area. If I can get the pdf to "look right" I can complete this project and have our IT team start uploading these pdf's.

 

[DanteAmor]

Add properties:

      wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & "\" & ky & ".pdf", _
         Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False