PDA

View Full Version : Exporting worksheet and overwriting



visible2you
08-30-2011, 12:55 PM
Hi,

I want to export a worksheet and overwrite a csv file. I heard that just like Menu -> File -> Save , I can too have Menu - > Export to csv

Just wanted to modify below program so that when I create same CSV file in second instance, then I don't face alert. And I can use Menu to execute below (or modified) program.

:doh:



Private Sub testme()
Dim newWks, wks As Worksheet
Dim filesname As Integer

filesname = 1
Set wks = ActiveWorkbook.Worksheets("Sheet1")
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks.SaveAs Filename:="C:\" & filesname, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
End Sub

sukumar.vb
08-30-2011, 01:14 PM
In this program, you will not require BolInProcess (http://www.vbaexpress.com/forum/showthread.php?t=38807) :banghead: which I came to know earlier. That BolInProcess (http://www.vbaexpress.com/forum/showthread.php?t=38807) was like :dunno and I was feeling like :motz2:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False 'avoid safety alert
With ActiveWorkbook
oldname = .Name
oldpath = .Path
oldformat = .FileFormat
.ActiveSheet.SaveAs Filename:="C:\Users\Hello\Desktop\file.csv", FileFormat:=xlCSV
.SaveAs Filename:=oldpath + "\" + oldname, FileFormat:=oldformat
End With
Application.DisplayAlerts = True
End Sub

GTO
08-30-2011, 01:31 PM
...I want to export a worksheet and overwrite a csv file. ...Just wanted to modify below program so that when I create same CSV file in second instance, then I don't face alert....

The code in your attached has slightly different naming and I copied from the workbook. If I am understanding, and you want the new csv to be named "1.csv", but not be alerted if overwriting a pre-existing, you just need to kill alerts for the SaveAs:
Option Explicit

Sub testme2()
Dim newWks As Worksheet
Dim wks As Worksheet
Dim getusername As Long
getusername = 1

Set wks = ActiveWorkbook.Worksheets("Sheet1")

wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
Application.DisplayAlerts = False
.SaveAs Filename:="C:\Users\Hello\Desktop\" & getusername, FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
End Sub


...I heard that just like Menu -> File -> Save , I can too have Menu - > Export to csv

...And I can use Menu to execute below ...

I'm not utterly sure, are you asking how to add a temporary command to the menubar? If so, is 2003 the latest version this would run in?

sukumar.vb
08-30-2011, 01:41 PM
Why do you need a command to Menubar?

visible2you
08-30-2011, 01:43 PM
Yes a temporary command to the menubar

Excel Version: 2002 or 2003.

GTO
08-30-2011, 02:35 PM
Okay, this is quickly put together; please test in a junk wb...

Note update to our SaveAs code:

In a Standard Module:

Option Explicit

Sub testme2()
Dim newWks As Worksheet
Dim wks As Worksheet
Dim getusername As Long

getusername = 1

Set wks = ActiveWorkbook.Worksheets("Sheet1")
'// DIsable events for the moment, so that ThisWorkbook Deactivate isn't fired. //
Application.EnableEvents = False
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
Application.DisplayAlerts = False
'change path back to suit, ie- "C:\Users\Hello\Desktop\" & getusername, FileFormat:=xlCSV
.SaveAs Filename:=ThisWorkbook.Path & "\" & getusername, FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
'// Re-enable events //
Application.EnableEvents = True
End Sub

In ThisWorkbook Module:
Option Explicit

'// Declared at module level, so Deactivate can access//
Dim NewBttn As CommandBarButton

Private Sub Workbook_Activate()
Dim MenuBar As CommandBar
Dim FileButton As CommandBarPopup
Dim SaveAsButtn As CommandBarControl
'// Set references to Menubar, "File" and "SaveAs" buttons. //
Set MenuBar = Application.CommandBars("Worksheet Menu Bar")
Set FileButton = MenuBar.FindControl(, 30002)
Set SaveAsButtn = MenuBar.FindControl(, 748, , , True)

'// Add a temp control and set its properties. Note that we do this in Activate instead //
'// of Open, so that we can rid the button if another wb is open in the same instance. //
Set NewBttn = FileButton.Controls.Add(msoControlButton, , , SaveAsButtn.Index + 1, True)
With NewBttn
.Caption = "Export to .csv"
.FaceId = 749
.OnAction = "testme2"
.TooltipText = "SaveAs to .csv"
.Style = msoButtonAutomatic
End With
End Sub

Private Sub Workbook_Deactivate()
'// Kill the button whenever another wb is activated or thisworkbook closes //
On Error Resume Next
NewBttn.Delete
On Error GoTo 0
End Sub

Hope that helps,

Mark