I am attempting to use savecopyas method in order to take a processed csv
file, to which I add additional sheets, including some output data and a
chart.

The problem occurs in that I've coded a form with the option of creating
a chart of the output. When I select the option to create a chart, the
processed csv file saves perfectly as an excel file (with the original
input data, output data, and a chart). However, when the option to chart
the data is not selected, the processed csv file only saves one of the
worksheets...and it's garbeled at that. Instead of the output being
displayed in four separate colums, all the data gets jammed into one
colum and separted by commas.

What's so bizarre is that the output routine occurs before that chart
routine and therefore independent of it.

Here is the code for the main body of the program. If someone would
desire to look at more, I'd be happy to forward an example of the input
data, an output file, even the rest of the code. And I'd be obliged to
receive any critique offered.

Thanks everyone for the great help you've been to my recent posts.

Smokii

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
Option Base 1

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'These variables declared here because they cannot be passed as arguments
between form
'and modules
Public ppi As Double
Public DispTol As Double
Public MaxErr As Double

Public NoXSec As Integer

Public cancelflag As Boolean
Public OpenFileflag As Integer

Public filelist() As String



Sub Main()

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
Dim ncracks As Integer

Dim i As Long

Dim Time1 As Double
Dim Runtime As Double

Dim vsplit As Variant

Dim cracks() As Double
ReDim cracks(1 To 3, 1 To 1000) As Double
'cracks(1,j) x
'cracks(2,j) y
'cracks(3,j) w

Dim xlsfilelist() As String

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'show Inputsettings user form
fmInputSettings.Show

'End routine if user clicks cancel
If cancelflag Then
End
End If

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Store InputSettings variables
ppi = fmInputSettings.tbppi.Value
DispTol = fmInputSettings.tbDispTol.Value
MaxErr = fmInputSettings.tbMaxErr.Value
NoXSec = fmInputSettings.tbNoXSec.Value


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Open File Options
Call Files(filelist, xlsfilelist)


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Start Timer
Time1 = Timer

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Turns off screen updating for faster processing
Application.ScreenUpdating = False

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Begin Processing

For i = 1 To UBound(filelist)
Workbooks.Open filename:=filelist(i)

ActiveSheet.Select


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
Call DEL_Error(MaxErr)
Call NewOD
Call cracksolve(NoXSec, DispTol, cracks, ncracks)
Call Out(cracks, ncracks)
If fmInputSettings.cbGraph Then Call Graph(ncracks)

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
With ActiveWorkbook
.SaveCopyAs filename:=xlsfilelist(i)
.Close savechanges:=False
End With


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Clear Cracks Array
ReDim cracks(1 To 3, 1 To 1000) As Double

Next i


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'End Timer
Runtime = Timer - Time1
MsgBox Runtime

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Turns on screen updating
Application.ScreenUpdating = True

End Sub

Without trying to recreate your code from the snippet you posted, why not just
use:

..saveas
(instead of .savecopyas)

Inside VBA's help, you'll see all the parms you can pass (including fileformat).

..savecopyas will keep the original workbook open with that name, but since
you're closing the workbook right after, it doesn't look like that would matter.



smokiibear wrote:

I am attempting to use savecopyas method in order to take a processed csv
file, to which I add additional sheets, including some output data and a
chart.

The problem occurs in that I've coded a form with the option of creating
a chart of the output. When I select the option to create a chart, the
processed csv file saves perfectly as an excel file (with the original
input data, output data, and a chart). However, when the option to chart
the data is not selected, the processed csv file only saves one of the
worksheets...and it's garbeled at that. Instead of the output being
displayed in four separate colums, all the data gets jammed into one
colum and separted by commas.

What's so bizarre is that the output routine occurs before that chart
routine and therefore independent of it.

Here is the code for the main body of the program. If someone would
desire to look at more, I'd be happy to forward an example of the input
data, an output file, even the rest of the code. And I'd be obliged to
receive any critique offered.

Thanks everyone for the great help you've been to my recent posts.

Smokii

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
Option Base 1

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'These variables declared here because they cannot be passed as arguments
between form
'and modules
Public ppi As Double
Public DispTol As Double
Public MaxErr As Double

Public NoXSec As Integer

Public cancelflag As Boolean
Public OpenFileflag As Integer

Public filelist() As String

Sub Main()

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
Dim ncracks As Integer

Dim i As Long

Dim Time1 As Double
Dim Runtime As Double

Dim vsplit As Variant

Dim cracks() As Double
ReDim cracks(1 To 3, 1 To 1000) As Double
'cracks(1,j) x
'cracks(2,j) y
'cracks(3,j) w

Dim xlsfilelist() As String

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'show Inputsettings user form
fmInputSettings.Show

'End routine if user clicks cancel
If cancelflag Then
End
End If

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Store InputSettings variables
ppi = fmInputSettings.tbppi.Value
DispTol = fmInputSettings.tbDispTol.Value
MaxErr = fmInputSettings.tbMaxErr.Value
NoXSec = fmInputSettings.tbNoXSec.Value

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Open File Options
Call Files(filelist, xlsfilelist)


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Start Timer
Time1 = Timer

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Turns off screen updating for faster processing
Application.ScreenUpdating = False

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Begin Processing

For i = 1 To UBound(filelist)
Workbooks.Open filename:=filelist(i)

ActiveSheet.Select


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
Call DEL_Error(MaxErr)
Call NewOD
Call cracksolve(NoXSec, DispTol, cracks, ncracks)
Call Out(cracks, ncracks)
If fmInputSettings.cbGraph Then Call Graph(ncracks)

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
With ActiveWorkbook
.SaveCopyAs filename:=xlsfilelist(i)
.Close savechanges:=False
End With


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Clear Cracks Array
ReDim cracks(1 To 3, 1 To 1000) As Double

Next i

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'End Timer
Runtime = Timer - Time1
MsgBox Runtime

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%
'Turns on screen updating
Application.ScreenUpdating = True

End Sub

--

Dave Peterson

I don't know another way to deal with the overwrite warning if the file I
want to save already exists. savecopyas seems to be the easiest way. in
either case, that shouldn't have an impact on whether the current sheet is
save or the whole workbook, right?

smokii

Dave Peterson wrote in
:

Without trying to recreate your code from the snippet you posted, why
not just use:

.saveas
(instead of .savecopyas)

Inside VBA's help, you'll see all the parms you can pass (including
fileformat).

.savecopyas will keep the original workbook open with that name, but
since you're closing the workbook right after, it doesn't look like
that would matter.

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.



smokiibear wrote:

I don't know another way to deal with the overwrite warning if the file I
want to save already exists. savecopyas seems to be the easiest way. in
either case, that shouldn't have an impact on whether the current sheet is
save or the whole workbook, right?

smokii

Dave Peterson wrote in
:

Without trying to recreate your code from the snippet you posted, why
not just use:

.saveas
(instead of .savecopyas)

Inside VBA's help, you'll see all the parms you can pass (including
fileformat).

.savecopyas will keep the original workbook open with that name, but
since you're closing the workbook right after, it doesn't look like
that would matter.





--

Dave Peterson

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.

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

I'll give your suggestion a try. However, I can and do open csv files and
create new worksheets within them all the time, manually. Maybe the issue
is how the data was genterated in the first place...

Dave Peterson wrote in
:

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).

But I bet when you open that CSV file, then change it, then save it, you make
sure you save it as a "microsoft excel workbook *.xls)"

If you just click on the Save icon, excel will even warn you that it can't save
all the stuff you might have done.

(and .savecopyas is a lot like clicking on the save icon (w/o the warning).)

smokiibear wrote:

I'll give your suggestion a try. However, I can and do open csv files and
create new worksheets within them all the time, manually. Maybe the issue
is how the data was genterated in the first place...

Dave Peterson wrote in
:

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).




--

Dave Peterson

Well...sometimes I'm a little slow...but the code I needed was indeed the
saveas. the following snippet enables me to overwrite without and alert
and save the csv to xls:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:="filename", FileFormat:=xlNormal
Application.DisplayAlerts = True

I thought that I had some overwrite issues with the saveas in a previous
try...but works like a charm.

Thanks :)

Smokii


Dave Peterson wrote in
:

Without trying to recreate your code from the snippet you posted, why
not just use:

.saveas
(instead of .savecopyas)

Inside VBA's help, you'll see all the parms you can pass (including
fileformat).

You could have used that Kill suggestion (in the earlier post), too.

Glad it's working.

smokiibear wrote:

Well...sometimes I'm a little slow...but the code I needed was indeed the
saveas. the following snippet enables me to overwrite without and alert
and save the csv to xls:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:="filename", FileFormat:=xlNormal
Application.DisplayAlerts = True

I thought that I had some overwrite issues with the saveas in a previous
try...but works like a charm.

Thanks :)

Smokii

Dave Peterson wrote in
:

Without trying to recreate your code from the snippet you posted, why
not just use:

.saveas
(instead of .savecopyas)

Inside VBA's help, you'll see all the parms you can pass (including
fileformat).


--

Dave Peterson