Error during saving a Excel sheet

  • 10 years ago
    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.
  • 10 years ago

    Hi,

    Please post the code you are using.

     

    I hope I will be able to help you.

  • 10 years ago
    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....
  • 10 years ago
    Did you get that sir?
  • 10 years ago

    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.

  • 10 years ago
    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...
  • 10 years ago

    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 If












    I hope this helps, otherwise let me know.

    Erwin

  • 10 years ago
    Thank you very much for both you... Now the program works nicely.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“XML is like violence - if it's not working for you, you're not using enough of it.”