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
...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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.