Macro Open Excel from FileExplorer, Save Tab as .prn File in different Folder

[Anthony Glenn]

Hello Forum,

I need help writing a macro/VBA code to open an Excel file saved in a File Explorer folder, then take a specific Tab from the Excel file and save it as a .prn file in a different File Explorer folder.
Much appreciated. Please let me know if you need any additional info.

[Bernie Deitrick]

Do the file, source folder, target folder, or tab ever change? OR are all those things variable, and need to be chosen by the user at the time the macro runs?

[Anthony Glenn]

Hello Bernie,

The source folder and target folder will always be the same. Tab names to be saved as .prn will also be the same. Only the file name will vary each time. I can have one Excel file in the source folder at a given time so the code could choose the one file in the source folder.

Thank you for your time, AG

[Bernie Deitrick]

You should be able to use the macro (with some modifications) posted by "beyond Excel"

[beyond Excel]

I show you a way to do it:

Sub Extract_and_Save_as_PRN()
Dim mPath, wb As Workbook
mPath = "Select a file type *.xl*"
If MsgBox(mPath, vbOKCancel) = vbCancel Then Exit Sub
mPath = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*", Title:=mPath)
If mPath = False Then Exit Sub
Set wb = Workbooks.Open(mPath, ReadOnly:=True)
Set mPath = Nothing: On Error Resume Next
Set mPath = Application.InputBox("Select any cell in the sheet to extract", Type:=8)
If mPath Is Nothing Then
  wb.Close False
  End
End If
On Error GoTo 0
Application.ScreenUpdating = False: DoEvents
mPath.Parent.Copy
mPath = ThisWorkbook.Path & "\" & ActiveSheet.Name & ".prn"
If Dir(mPath) <> "" Then
  Kill mPath
  Application.Wait DateAdd("s", 1, Now)
End If
ActiveWorkbook.SaveAs mPath, FileFormat:=xlTextPrinter
ActiveWorkbook.Close False
wb.Close False
MsgBox "Saved file '" & mPath & "'"
End
End Sub

[Anthony Glenn]

Hi again, thank you for your help. I would to streamline this workflow: from within the Excel file itself, I would simply like for the macro to first find the tab named "Title" and another tab named "Script" and save each tab as individual .prn files with different file names "TitleABC.prn" and "ScriptABC.prn" in a specific specified folder (always same location). Additionally, can the macro be launched by typing "Ctrl-T"?

[beyond Excel]

Hi again, thank you for your help.

Who are you referring to in your message?...

[Anthony Glenn]

Thanks in advance, beyond Excel. If you could help with the coding, I would very much appreciate it. AG

[beyond Excel]

Thanks in advance, beyond Excel. If you could help with the coding, I would very much appreciate it. AG

I don't understand:
- Did you see the code of my message #3?
- Do you want me to help you with some of said code?...

[Anthony Glenn]

Hello beyond Excel, yes, may I ask for help with the code you provided? From within the Excel file itself, I would simply like for the macro to first find the tab named "Title" and another tab named "Script" and save each tab as individual .prn files with different file names "TitleABC.prn" and "ScriptABC.prn" in a specific specified folder (always same location). Additionally, can the macro be launched by typing "Ctrl-T"?