Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing macros in excel sheet | Excel Worksheet Functions | |||
writing macros in excel | Excel Programming | |||
Writing Excel Macros in VB.NET/C# | Excel Programming | |||
Need help writing basic macros in EXCEL.. | Excel Discussion (Misc queries) | |||
Writing Macros in Excel | Excel Programming |