savecopyas issues
I think I still have questions on how you're creating that new worksheet with
output data.
If you're opening a .csv file, then no matter what you do to that file, it's
still a .csv file to excel (well, until you SaveAs as different file format).
If you're actually creating a new workbook:
dim NewWkbk as workbook
set newwkbk = workbooks.add
Then this is a normal excel file.
If you're opening a .csv file, then it'll be a xlCSV file.
Ahhhh.
If you're opening a .csv file, try creating a new worksheet in a new workbook
and copy the data to that new sheet. Then save from there. (just as a test to
see if it works for you.)
Option Explicit
Sub testme()
Dim myCSVWks As Worksheet
Dim newWks As Worksheet
Workbooks.Open Filename:="C:\My Documents\excel\book2.csv"
Set myCSVWks = ActiveSheet
MsgBox myCSVWks.Parent.FileFormat '6 = xlCSV
'mycsvwks.Parent.savecopyas would still be the same format
Set newWks = Workbooks.Add(1).Worksheets(1)
MsgBox newWks.Parent.FileFormat '-4143 is xlNormalWorkbook
myCSVWks.UsedRange.Copy _
Destination:=newWks.Range("a1")
MsgBox newWks.Parent.FileFormat 'just to show it didn't change.
'newwks.Parent.savecopyas would be xlnormal
End Sub
Maybe that explains it better.
===
And if I added a chart to a file that was xlCSV format, then when I did
..savecopyas, it resulted in a text file that was readable in Notepad. Same
thing when I didn't add a chart. (xl2002)
smokiibear wrote:
Thanks for all you time Dave...I really appreciate your help.
One question I still have regards the following distinction:
Option 1)
-create new worksheet with output data
-call graph routine (which creates chart on new worksheet, its source
from output data)
-savecopyas csv to xls
Option 2)
-create new worksheet with output data
-bypass graph routine
-savecopyas csv to xls
option 1) works 100%, while option 2) yields a garbeled file.
Should creating an additional worksheet of type chart have anything to do
with how the file saves?
smokii
Dave Peterson wrote in
:
When you do savecopyas, it saves an exact copy of that file. If the
file is still csv (even with multiple worksheets and charts, it's
still a .csv file), it'll be csv when you're done.
If it's really a workbook, it'll be a workbook.
You could check the fileformat with something like:
MsgBox ActiveWorkbook.FileFormat
And if you go into the object browser (F2 in the VBE), you can search
for fileformat.
You'll see all those constants (like xlcsv) and at the bottom of the
screen, you'll see the number that corresponds to each of the entries
you select.
FYI:
Const xlCSV = 6
Const xlWorkbookNormal = -4143 (&HFFFFEFD1)
=====
You could kill the existing file before you save:
On Error Resume Next
Kill xlsfilelist(i)
On Error GoTo 0
With ActiveWorkbook
.SaveAs Filename:=xlsfilelist(i)
.Close savechanges:=False
End With
Or you could just suppress that warning:
With ActiveWorkbook
application.displayalerts = false
.SaveAs Filename:=xlsfilelist(i)
application.displayalerts = true
.Close savechanges:=False
End With
======
Ps. If you look at that "garbled" workbook in NotePad, you'll see
that it's still a comma separated values file.
--
Dave Peterson
|