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.

 

[dmj120]

I tried that - doesn't change the pdf view. The only difference from the screenshot that I've been able to achieve is by changing the excel file properties to 'not wrap' ....then the text goes off the pdf page, but the second column is still the same width (the boarders on the screenshot).

Add properties:

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

 

[DanteAmor]

This is my result:

 

[dmj120]

Thanks. Now I'm really lost. But it must be something with my adobe.

Thanks for all your help!

This is my result:

 

[dmj120]

Man, I do NOT understand where the issue is.... ?

this is the best pdf I've gotten - which does not wrap correctly:

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:\Users\Josh\Dropbox\Work\JobPlanPdfs\"
 
  Set sh = Sheets("Job Plan 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", _
         Quality:=xlQualityStandard, IncludeDocProperties:=True, _
         IgnorePrintAreas:=False, OpenAfterPublish:=False
      wb.Close False
    Next ky
  End With
  sh.Select
  sh.ShowAllData
  Application.ScreenUpdating = True
End Sub

 

[AC PORTA VIA]

I got same output as

DanteAmor​

The only thing I can't figure out is to have task # on the first line if there is more than one row for same task

 

[dmj120]

Thanks! I was actually gonna bump this to see if anyone has any idea why Dante [and now you] print to pdf correctly by my system is lost-in-da-sauce. ?

The task# does show on each line, as you'd expect. I just CANNOT see why my pdf doesn't print correctly. I've tried my laptop (excel 2019), desktop (365)....hell, gonna try my wife's 2010 version.

The only thing I've come up with is when the "second excel" opens/prints, some setting on the original file is included which causes adobe to freak out.... I think I'm going to try putting Nitro as my default to see if that works better.

If I can't get this to work, I'm going to have to try creating text files, which I think will be a frickin nightmare!


..........it really sucks when there's one small issue jackin' up, in an otherwise tasty bit of code!!!

I got same output as

DanteAmor​

The only thing I can't figure out is to have task # on the first line if there is more than one row for same task