writing excel macros
 

Hello! I am not sure if I need to edit the existing macro, but here is the
deal: I have an Excel spreadsheet which runs a macro to save as a csv
document. Is there a way I can edit the macro or something I need to do
elsewhere so that the csv will only display data from Excel rows with data?
Right now I get my Excel data in the csv and followed by pages of just commas
from rows without data. Thank you very much for your help and your time. If
you are explaining any editing to macros, please use baby-steps as I have not
worked with any sort of macro writing before. The one I have is pre-existing
by another user (no longer available to me to help).

writing excel macros
 

Post what you have at the moment, as long as it's not pages long.

NickHK

"TMiGNa" ...
Hello! I am not sure if I need to edit the existing macro, but here is
the
deal: I have an Excel spreadsheet which runs a macro to save as a csv
document. Is there a way I can edit the macro or something I need to do
elsewhere so that the csv will only display data from Excel rows with
data?
Right now I get my Excel data in the csv and followed by pages of just
commas
from rows without data. Thank you very much for your help and your time.
If
you are explaining any editing to macros, please use baby-steps as I have
not
worked with any sort of macro writing before. The one I have is
pre-existing
by another user (no longer available to me to help).

writing excel macros
 

"NickHK" wrote:

Post what you have at the moment, as long as it's not pages long.

NickHK
-----------------------------------------------------------------------------------------

Sub Save_EF_as_csv()
'
' Save_EF_as_csv Macro
' Macro recorded 2/4/2005 by KClifford
'

Sheets("Electronic File").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(""),
FileFormat:= _
xlCSV
ActiveWindow.Close , SaveChanges:=False
Sheets("Print File").Select
End Sub

writing excel macros
 

Make a back up of your original file first, then replace your code with
this.
Does this achieve your aim ?

Sub Save_EF_as_csv()
Dim RetVal As Variant

Sheets("Electronic File").Copy
RetVal = Application.GetSaveAsFilename(, "CSV File (*.csv),*.csv", , "Select
a file name and location")
If RetVal = False Then Exit Sub

ActiveWorkbook.SaveAs RetVal, xlCSV
ActiveWorkbook.Close False

Sheets("Print File").Select
End Sub

NickHK

"TMiGNa" ...
"NickHK" wrote:

Post what you have at the moment, as long as it's not pages long.

NickHK
-----------------------------------------------------------------------------------------

Sub Save_EF_as_csv()
'
' Save_EF_as_csv Macro
' Macro recorded 2/4/2005 by KClifford
'

Sheets("Electronic File").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(""),
FileFormat:= _
xlCSV
ActiveWindow.Close , SaveChanges:=False
Sheets("Print File").Select
End Sub

writing excel macros
 

I'm still getting all the excess commas - sorry...

"NickHK" wrote:

Make a back up of your original file first, then replace your code with
this.
Does this achieve your aim ?

Sub Save_EF_as_csv()
Dim RetVal As Variant

Sheets("Electronic File").Copy
RetVal = Application.GetSaveAsFilename(, "CSV File (*.csv),*.csv", , "Select
a file name and location")
If RetVal = False Then Exit Sub

ActiveWorkbook.SaveAs RetVal, xlCSV
ActiveWorkbook.Close False

Sheets("Print File").Select
End Sub

NickHK

"TMiGNa" ...
"NickHK" wrote:

Post what you have at the moment, as long as it's not pages long.

NickHK
-----------------------------------------------------------------------------------------

Sub Save_EF_as_csv()
'
' Save_EF_as_csv Macro
' Macro recorded 2/4/2005 by KClifford
'

Sheets("Electronic File").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(""),
FileFormat:= _
xlCSV
ActiveWindow.Close , SaveChanges:=False
Sheets("Print File").Select
End Sub

writing excel macros
 

Try resetting the used range before you create the .csv file.

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused

TMiGNa wrote:

I'm still getting all the excess commas - sorry...

"NickHK" wrote:

Make a back up of your original file first, then replace your code with
this.
Does this achieve your aim ?

Sub Save_EF_as_csv()
Dim RetVal As Variant

Sheets("Electronic File").Copy
RetVal = Application.GetSaveAsFilename(, "CSV File (*.csv),*.csv", , "Select
a file name and location")
If RetVal = False Then Exit Sub

ActiveWorkbook.SaveAs RetVal, xlCSV
ActiveWorkbook.Close False

Sheets("Print File").Select
End Sub

NickHK

"TMiGNa" ...
"NickHK" wrote:

Post what you have at the moment, as long as it's not pages long.

NickHK
-----------------------------------------------------------------------------------------

Sub Save_EF_as_csv()
'
' Save_EF_as_csv Macro
' Macro recorded 2/4/2005 by KClifford
'

Sheets("Electronic File").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(""),
FileFormat:= _
xlCSV
ActiveWindow.Close , SaveChanges:=False
Sheets("Print File").Select
End Sub







--

Dave Peterson

writing excel macros
 

Check that cells in Excel do not contain spaces or some other non printing
characters.
You may get some extra blank columns, because of the way Excel parses the
data (in blocks of 16 rows), but this should not affect the whole row.

NickHK

"TMiGNa" wrote in message
...
I'm still getting all the excess commas - sorry...

"NickHK" wrote:

Make a back up of your original file first, then replace your code with
this.
Does this achieve your aim ?

Sub Save_EF_as_csv()
Dim RetVal As Variant

Sheets("Electronic File").Copy
RetVal = Application.GetSaveAsFilename(, "CSV File (*.csv),*.csv", ,
"Select
a file name and location")
If RetVal = False Then Exit Sub

ActiveWorkbook.SaveAs RetVal, xlCSV
ActiveWorkbook.Close False

Sheets("Print File").Select
End Sub

NickHK

"TMiGNa"
...
"NickHK" wrote:

Post what you have at the moment, as long as it's not pages long.

NickHK

--------------------------------------------------------------------------
---------------

Sub Save_EF_as_csv()
'
' Save_EF_as_csv Macro
' Macro recorded 2/4/2005 by KClifford
'

Sheets("Electronic File").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(""),
FileFormat:= _
xlCSV
ActiveWindow.Close , SaveChanges:=False
Sheets("Print File").Select
End Sub

writing excel macros
 

And there was code in that link to Debra's site that could be used to reset the
last used cell.



TMiGNa wrote:

A reply to both NickHK and Dave Peterson:

I cleaned up the Excel doc as suggested. Those helped, but still gave some
of the commas from the sheet (named EF) that pulls data from the Excel table
to export to the csv. With using the form daily, deleting the enormous number
of surplus rows (which can be as many as 6000) in the EF is a large task.
I'm not allowed to simply shorten that sheet since we would never have that
many rows of data, which would make so much sense (offices - go figure) so
right now I am holding shift and pressing control end to select all the extra
and delete it. That works, though something more concise would be wonderful -
I'd love it if I could crop to the data like one does to an image.
Everyone's suggestions were very helpful to me since I am learning these
tasks anew, so I appreciate all the input I've received. Soo.. I have an
immediate quick fix for my commas, but I'm still open to any ideas to try
since I am still learning.

"NickHK" wrote:

Check that cells in Excel do not contain spaces or some other non printing
characters.
You may get some extra blank columns, because of the way Excel parses the
data (in blocks of 16 rows), but this should not affect the whole row.


"Dave Peterson" wrote:

Try resetting the used range before you create the .csv file.

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused

--

Dave Peterson

writing excel macros
 

Ah - see it. So here's where my newbie questions come in, so please be
patient with me.... would a plug that code in as a new macro, or add it to
the already existing macro? If the later, then how should it be added to that
code? My guess is as a new macro, but can a document utilise multiple macros?
Please forgive my lack of knowledge. Thanks!

"Dave Peterson" wrote:

And there was code in that link to Debra's site that could be used to reset the
last used cell.

writing excel macros
 

The subroutine "Sub DeleteUnused()" on Debra's site actually tries to reset the
last used cell of all the worksheets in the active workbook.

So you could make that just another procedure in your project (at the bottom of
the module -- or in a whole different module).

Then just add:

call Sub DeleteUnused

to your existing code (as long as the workbook to "clean" is active).

So you can do it beforehand -- right at the start???.

Or do it right after you copy to the new workbook and just clean up that
workbook with a single sheet.

=======
Personally???

I have this same kind of macro in my personal.xl* file. I have it assigned to a
short cut key (ctrl-t isn't used by excel, so I use that).

I find it useful for lots and lots of workbooks and just run it when I want. In
my case, I'd run my personal.xl* version (via the shortcut key), then run your
existing code.

If I have to share with others, I'd embed it into the routine (with "call Sub
DeleteUnused").




TMiGNa wrote:

Ah - see it. So here's where my newbie questions come in, so please be
patient with me.... would a plug that code in as a new macro, or add it to
the already existing macro? If the later, then how should it be added to that
code? My guess is as a new macro, but can a document utilise multiple macros?
Please forgive my lack of knowledge. Thanks!

"Dave Peterson" wrote:

And there was code in that link to Debra's site that could be used to reset the
last used cell.



--

Dave Peterson

writing excel macros
 

I have the code installed, but when I try to run it, I get the message
"run-time error '1004': Application-defined or object-defined error" and the
debugger highlights this part of the code. Do I need to add or remove
something? Thanks for your patience with me.

Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete

"Dave Peterson" wrote:

The subroutine "Sub DeleteUnused()" on Debra's site actually tries to reset the
last used cell of all the worksheets in the active workbook.

So you could make that just another procedure in your project (at the bottom of
the module -- or in a whole different module).

Then just add:

call Sub DeleteUnused

to your existing code (as long as the workbook to "clean" is active).

So you can do it beforehand -- right at the start???.

Or do it right after you copy to the new workbook and just clean up that
workbook with a single sheet.

=======
Personally???

I have this same kind of macro in my personal.xl* file. I have it assigned to a
short cut key (ctrl-t isn't used by excel, so I use that).

I find it useful for lots and lots of workbooks and just run it when I want. In
my case, I'd run my personal.xl* version (via the shortcut key), then run your
existing code.

If I have to share with others, I'd embed it into the routine (with "call Sub
DeleteUnused").




TMiGNa wrote:

Ah - see it. So here's where my newbie questions come in, so please be
patient with me.... would a plug that code in as a new macro, or add it to
the already existing macro? If the later, then how should it be added to that
code? My guess is as a new macro, but can a document utilise multiple macros?
Please forgive my lack of knowledge. Thanks!

"Dave Peterson" wrote:

And there was code in that link to Debra's site that could be used to reset the
last used cell.



--

Dave Peterson

writing excel macros
 

First, I pasted too much:

call Sub DeleteUnused
should be
call DeleteUnused

====
And I've used that code lots of times without error.

But remember that since it's deleting rows, it can only work against an
unprotected worksheet.

Any chance that your sheet is protected?

This minor tweak will stop the error if a worksheet is protected.

Option Explicit
Sub DeleteUnused()

Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
With wks
If wks.ProtectContents = True _
Or wks.ProtectDrawingObjects = True _
Or wks.ProtectScenarios = True Then
'do nothing
Else
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End If
End With
Next wks

End Sub


TMiGNa wrote:

I have the code installed, but when I try to run it, I get the message
"run-time error '1004': Application-defined or object-defined error" and the
debugger highlights this part of the code. Do I need to add or remove
something? Thanks for your patience with me.

Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete

"Dave Peterson" wrote:

The subroutine "Sub DeleteUnused()" on Debra's site actually tries to reset the
last used cell of all the worksheets in the active workbook.

So you could make that just another procedure in your project (at the bottom of
the module -- or in a whole different module).

Then just add:

call Sub DeleteUnused

to your existing code (as long as the workbook to "clean" is active).

So you can do it beforehand -- right at the start???.

Or do it right after you copy to the new workbook and just clean up that
workbook with a single sheet.

=======
Personally???

I have this same kind of macro in my personal.xl* file. I have it assigned to a
short cut key (ctrl-t isn't used by excel, so I use that).

I find it useful for lots and lots of workbooks and just run it when I want. In
my case, I'd run my personal.xl* version (via the shortcut key), then run your
existing code.

If I have to share with others, I'd embed it into the routine (with "call Sub
DeleteUnused").




TMiGNa wrote:

Ah - see it. So here's where my newbie questions come in, so please be
patient with me.... would a plug that code in as a new macro, or add it to
the already existing macro? If the later, then how should it be added to that
code? My guess is as a new macro, but can a document utilise multiple macros?
Please forgive my lack of knowledge. Thanks!

"Dave Peterson" wrote:

And there was code in that link to Debra's site that could be used to reset the
last used cell.



--

Dave Peterson


--

Dave Peterson