VBA: extract to pdf/txt

[Archangelos]

I have an excel workbook with a four sheets. I need to extract them with the proper filename, in a specific folder. Until now, I did it manually but ... ... I have a little knowledge of VBA so why not do it automatically?

The filename is: "testIntGold_1708261850.xlsm"

The numeric part of the filename name is the data/time information of the file (the above means that the file was made on 26/08/2017, 18:50). I have one sheet named "File" that must be saved as pdf and the filename should be "IntGold-IOdoc_1708261850.pdf". Another sheet named "connections" should be extracted to pdf named as "IntGold-Conn_1708261850.pdf". The third sheet to be saved as pdf is named "Bypass" and the filename should be "IntGold-Bypass_1708261850.pdf". Finally, the sheet named "RawData" should be saved as tab separated text file and the filename should be "IntGold-RawData_1708261850.txt".

This is the code I wrote an hour ago.

Sub aaExtract()Dim WorkbookName As String
Dim SaveDir As String
Dim OutputFilename As String


SaveDir = "%USERPROFILE%\Desktop" & "\toEmail\_00_AF\"


  'Get date/time info string from filename
    WorkbookName = ActiveWorkbook.Name
    WorkbookName = Replace(WorkbookName, ".xlsm", "")
    WorkbookName = Replace(WorkbookName, ".xlsx", "")
    WorkbookName = Replace(WorkbookName, "testIntGold_", "")


  'Save IO doc
   OutputFilename = SaveDir & "IntGold-IOdoc_" & WorkbookName & ".pdf"
   Sheets("File").Activate
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    OutputFilename, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


  'Save Label
   OutputFilename = SaveDir & "IntGold-Label_" & WorkbookName & ".pdf"
   Sheets("Label").Activate
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    OutputFilename, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


  'Save Connections
   OutputFilename = SaveDir & "IntGold-Conn_" & WorkbookName & ".pdf"
   Sheets("Connections").Activate
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    OutputFilename, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


  'Save Bypass
   OutputFilename = SaveDir & "IntGold-Bypass_" & WorkbookName & ".pdf"
   Sheets("ByPass").Activate
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    OutputFilename, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


  'Save eaw data  IntGold-RawData
   OutputFilename = SaveDir & "IntGold-Conn_" & WorkbookName & ".txt"
   Sheets("RawData").Activate
   ActiveWorkbook.SaveAs Filename:=OutputFilename, _
   FileFormat:=xlText, CreateBackup:=False

End Sub

PS: would it be possible to attach a file in the post?

 

[Archangelos]

Sometimes, the following command refuses to work.

 Sheets("File").Activate