Saved Worksheet looks like garbage/New problem

[CyberJar]

Hello,

I attempt to save a Worksheet to the Users PC using the following code (commented out stuff to get the bare-boned code to work), but the output looks like garbage. This is an Excel/VBA app run on a web server, and this button is on a UserForm.

Private Sub cmdSave_Click()
Dim varFileName As Variant
Dim NumRows As Long, NumCols As Integer
Dim r As Long, c As Integer
Dim data
 
On Error GoTo 300
NumCols = Columns.Count
NumRows = Rows.Count
ChDir "C:\"
varFileName = Application.GetSaveAsFilename("MyReport.txt", "Text Files (*.txt),*.txt," & _
"Print Files(*.prn),*.prn", 1, "MyReport")
If (VarType(varFileName) = vbString) Then
 
Open varFileName For Output As #1
' For r = 1 To NumRows
'  For c = 1 To NumCols
'  data = Cells(r, c).Value
'  If c <> NumCols Then
   Print #1, data;
'  Else
'   Print #1, data
'  End If
'  Next c
' Next r
Close #1
 
ActiveWorkbook.SaveAs FileName:=varFileName
Exit Sub
300:
MsgBox Err.Number & " " & Err.Description
Resume Next
End If
End Sub

When the User clicks a button, they should be prompted as to where to save the file (this works fine) then I have to save it there.
Should the line that says, "ActiveWorkbook.SaveAs etc." be "ActiveWorkbook("Worksheet Name").SaveAs etc." ?

Thank you for any help,
CJ

[RobDog888]

ActiveWorkbook does not take any indexes because it is supossed to be for the active workbook - no need to tell it which
one. You can use this to specify a different workbook.

Application.Workbooks("Book1").SaveAs varFileName

[CyberJar]

Thanks for your reply.

I won't index the ActiveWorkbook, but
can you tell why the output is coming out as garbage?

Thank you,
CJ

[RobDog888]

I think (without actually seeing the results) that you are now
saving the file in any type of format. Just writting out certain cell
contents. Try saving the workbook in something like Excel format.
Either xlExcel7 or xlCSV.

[TheFIDDLER]

Try using a .xls extension to save as an excel format file.

.txt saves it as space delimited and .prn saves it as tab.delimited which means that unless you are a piece of code looking for data, it's not meant to be pretty. These are used mostly to import or export data between different applications, mostly older programs.

[CyberJar]

Thanks for the help guys.

The problem was - I was not using the FIleFormat Property of the SaveAs method. This solved it:

objWS.SaveAs FileName:=varFileName, FileFormat:=xlTextPrinter

Then in GetSaveAsFilename I only allow .PRN for FileFilter. My downloaded file is looking good.

One thing:
Can I format the file before using SaveAs? Suppose I'd like to make the Font Size smaller so the data can fit on 1 printed page, or Zoom to 50%, do I use PageSetUp ? Seems odd because one associates PageSetup with Printing - not downloading a Worksheet, but the Worksheet *is* for Downloading and possible printing at a later time.

Please advise,
CyberJar

[RobDog888]

Seems odd because one associates PageSetup with Printing - not downloading a Worksheet

The page setup is to format the page either for margins or orientation, etc.
whether it gets printed or not - hence the word page setup. If it
was print setup (properties) then it would refer to setting up the
printer, not the page to be printed.

To answer you last question, you could format the page as you
wanted it to be for the .SaveAs method and that will be how it will
be .SaveAs and when you close the workbook just do not save
your changes so the next time you open it, it will have the
original page setup and formatting, etc.

[CyberJar]

I spoke too soon. On further inspection, the SAVEAS method is trying to Save the Worksheet back into the Xls - with a new name. If the Sheet name changes - the app won't run!

This is unwanted behavior.

The msdn definition of SaveAs says "Saves changes to the sheet (syntax 1) *in a different file*" This is the behavior I expected.

Private Sub cmdSave_Click()
Dim varFileName As Variant

On Error GoTo 300
ChDir "C:\"
varFileName = Application.GetSaveAsFilename("My Report", "Print File(*.prn),*.prn", , "Save Output")
If (VarType(varFileName) = vbString) Then
OWS.SaveAs FileName:=varFileName, FileFormat:=xlTextPrinter
Exit Sub
End If

300:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

So if my Worksheet is called "Output", after the SaveAs it's now called "My Report" - actually taken from GetSaveAsFilename. In debug I can see varFileName is set to "C:\My Report.prn"
BTW: OWS is an Excel.Worksheet set to the Worksheet Output.

TIA,
CJ

[CyberJar]

Just noticed that under Tools/Options, there's a "Transition" tab where you can set "Save Excel files as". Mine is set to Microsoft Excel Workbook. All of the FileFormat:= settings are listed.

Do I need to set this option to "Formatted Text (space delimited) to stop SaveAs from trying to save the Worksheet back into the Workbook with a new name as stated above?

Thanks,
CJ