Wrox Programmer Forums
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 15th, 2007, 01:33 AM
Registered User
Join Date: May 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default how to save as an excel sheet

I have two Q.
1. I want to save excel sheet through VB6 and when the following massage come to me (the following file already exist do you want to replace it?? Yes NO Cancel)
This massage I don't want to see it any more I want to save the file every day and save the file over the existing file without asking me do you want to replace the file. The codes I use are:
Dim a As Excel.Application
 Dim b As New Excel.Workbook, b1 As New Excel.Workbook
 Dim c As New Excel.Worksheet, c1 As New Excel.Worksheet
 Set a = New Excel.Application
s1 = "C:\Documents and Settings\cmmsbatch\Desktop\sec.csv"
Dim i As Long
Dim pos As Long, s As Long
Dim arr As Variant
Set b = a.Workbooks.Open(s1)
' this is the line i want to change it
b.SaveAs ("C:\Documents and Settings\cmmsbatch\Desktop\sec-users.csv")
Set c = b.Sheets(1)
i = 2
While c.Cells(i, 1) <> ""
    arr = Split(CStr(c.Cells(i, 1)), ",")
c.Cells(i, 2) = CStr(arr(UBound(arr)))
   c.Cells(i, 3) = CStr(arr(UBound(arr) - 1))
   i = i + 1
c.[1:1].Delete Shift:=xlUp
SendKeys "{ENTER}"
End Sub
2. The second question I want to delete specific excel sheet file then save other file into specific place.

Best Regard,
Old May 19th, 2007, 11:00 PM
Registered User
Join Date: May 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

No Answer

Old May 25th, 2007, 09:48 AM
Registered User
Join Date: May 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Here it is - just prior to the saveas, you can turn off alerts - you can then turn them back on if necessary..

Dim XLapp As Excel.Application

Set XLapp = CreateObject("Excel.Application")

XLapp.DisplayAlerts = False

For #2, I would use the file scripting object or a file deletion API- would not try to do it using excel methods.

Good luck


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
linking information to another sheet and save it monicaspinder Beginning VB 6 1 June 29th, 2007 04:55 AM
to save datagrid content to a excel sheet josna VB.NET 1 March 4th, 2006 07:10 AM
write multi-sheet Excel w/o Excel.Application manmoth Classic ASP Components 2 November 22nd, 2005 10:56 AM
save Excel spread-sheet to SQL Server ybao Classic ASP Databases 1 July 12th, 2004 03:02 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.