Error during saving a Excel sheet
-
Hi every one,
In my project i am using Microsoft Excel9.0 as referance for making a Excel sheet and converting the records from a Access database in to a Excel file. Everything is working fine up to creating a Excel sheet, and storing the records in it.The problem is that, when i save a file with an Existing file name it shows error.
Please tell me how to solve this Error...
Thank you. -
Hi,
Please post the code you are using.
I hope I will be able to help you.
-
CmnDia.ShowSave FileName = CmnDia.FileName If FileName = "" Then Exit Sub End If Set oXLApp = New Excel.Application 'Create a new instance of Excel Set oXLBook = oXLApp.Workbooks.add 'Add a new workbook Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet iSheetsPerBook = oXLApp.SheetsInNewWorkbook oXLApp.SheetsInNewWorkbook = 1 oXLApp.SheetsInNewWorkbook = iSheetsPerBook Set Rs_Tmp = New Recordset Rs_Tmp.Open "select * from callerdetails", INFO, adOpenDynamic, adLockOptimistic oXLSheet.Cells(2, 1) = "Callers Phone" oXLSheet.Cells(2, 2) = "Mobile" oXLSheet.Cells(2, 3) = "Callers Name" oXLSheet.Cells(2, 4) = "Company" oXLSheet.Cells(2, 5) = "Company Address#1" oXLSheet.Cells(2, 6) = "Company Address#2" oXLSheet.Cells(2, 7) = "Company Address#3" oXLSheet.Cells(2, 8) = "City" oXLSheet.Cells(2, 9) = "Pincode" oXLSheet.Cells(2, 10) = "EMail ID" oXLSheet.Cells(2, 11) = "Remarks" oXLSheet.Cells(2, 12) = "Name" oXLSheet.Cells(2, 13) = "Address#1" oXLSheet.Cells(2, 14) = "Address#2" oXLSheet.Cells(2, 15) = "Address#3" oXLSheet.Cells(2, 16) = "Telephone" oXLSheet.Cells(2, 17) = "Description" oXLSheet.Cells(2, 18) = "Pincode" oXLSheet.Cells(2, 19) = "Spac1" oXLSheet.Cells(2, 20) = "Spac2" oXLSheet.Cells(2, 21) = "Spac3" oXLSheet.Cells(2, 22) = "Spac4" oXLSheet.Cells(2, 23) = "Spac5" oXLSheet.Cells(2, 24) = "Spac6" oXLSheet.Cells(2, 25) = "Spac7" oXLSheet.Cells(2, 26) = "Spac8" oXLSheet.Cells(2, 27) = "Spac9" oXLSheet.Cells(2, 28) = "Spac10" oXLSheet.Cells(2, 29) = "Spac11" oXLSheet.Cells(2, 30) = "Spac12" oXLSheet.Cells(2, 31) = "Spac13" oXLSheet.Cells(2, 32) = "Spac14" oXLSheet.Cells(2, 33) = "Spac15" oXLSheet.Cells(2, 34) = "Spac16" oXLSheet.Range("A4", "A3").CopyFromRecordset Rs_Tmp oXLApp.Visible = True 'Show it to the user Set oXLSheet = Nothing 'disconnect from the Worksheet oXLBook.SaveAs FileName 'oXLBook.Close SaveChanges:=False Set oXLBook = Nothing 'Disconnect from Excel (let the user take over) 'oXLApp.Quit Set oXLApp = Nothing 'Disconnect from Excel (let the user take over) ' cmd_TrackCall.Enabled = Not cmd_TrackCall.Enabled ' Frme_INFOLINE.Enabled = Not Frme_INFOLINE.Enabled This is the code with i used in my program....
-
Did you get that sir?
-
Hi,
I didn't receive any error noting that I didn't exactly used all your code because it is hard to restructure. In all cases I advise you to use the below decleration and initilization for excel without referencing the excel Library. If problem presists please send the error syntex as well as the complete structured code.
Dim OXLAPP As Object
Dim oXLBook As Object
Dim oXLSheet As Object
Set OXLAPP = CreateObject("Excel.Application")
Set oXLBook = OXLAPP.Workbooks.Open("C:\BOOK1.xls")
Set oXLSheet = oXLBook.Sheets.Item(1)Hope this works for you.
-
Hello sir, I dont have any problem in creating Excel or in my codes. while Saving the Excel file using CommonControlDialog box, if i save the excel file using a name which is already exsits in that folder, it whether to replace the existing file with the new file. If i press no in that dialog box, it shows error. Please tell me how to check the files in that folder, with the file name that we give in the text box or to replace directly with the new file...
-
Hi,
There are a few things to consider:
First, if I understand you properly, you are using the common dialog to get a path & filename from the user. Through this dialog, you should get the "File exists, overwrite..." prompt. Saying no, will bring you back in the dialog to give another name.
Second, once you've left the common dialog, there are only two options: either the user has given you a filename (and OK'd overwriting if it is an existing file), or the user cancelled.
Third, you can disable the default Excel overwrite prompt. Note that this is an application wide setting that is stored in the registry, and should be reset after use.
So if you check how the user left the dialog, you should be OK in saving without the default Excel overwrite prompt. In code this should look something like this:
On Error Resume Next
dlgCommonDialog.ShowSave
'Check if the user left the dialog through the Cancel-button
If Err.Number = 32755 Then
'Do nothing & re-enable error-handling
On Error GoTo 0
Else
sFileName = dlgCommonDialog.FileName
'Temporarily disable Excel overwrite prompt
Application.displayAlerts = False
oExcel.Workbooks(1).SaveAs sFileName
'Re-enable Excel overwrite prompt
Application.DisplayAlerts = True
End IfI hope this helps, otherwise let me know.
Erwin
-
Thank you very much for both you... Now the program works nicely.
Post a reply
VB 6 forum discussion
-
CorelDRAW VBA: cdrTraceLineDrawing FAILS, producing single linear path instead of Centerline trace?
by dancemanj (0 replies)
-
client/server application using activex
by beautifulheart (0 replies)
-
System Error &H8007007E. The specifed module could not be found.
by swiftsafe (5 replies)
-
Invitation to take part in an academic research study
by researchlab (0 replies)
-
Send SMS with SMPP
by mmahmoud (0 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).