How to save Excel file via VB 2010 without any dialogs (like "save as")

I am trying to save an Excel file through VB 2010 and I have these questions.

  • How do I disable the "Save As" dialog? I've tried things like "save" instead of "save as" but it doesn't work ...

  • After I have saved the file (using save as) I cannot delete it ... (I tried to close the excel file, Visual Basic, etc.) all I get is an error saying it is allready open in excel ...

  • Is there a way to get VB to show me tips for writing excel stuff (ie when I write a Messagebox.) It pops up "Show" for reference. how can i enable this for excel code [.cells sheets. ect.))

compound:

Sub Connect()
    ' Connect to the excel file
    oExcel = CreateObject("Excel.Application")
    'Devine the  workbook
    oBook = oExcel.workbooks.open("e:\Words\Heb.xls")
End Sub

      

saveas:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    oExcel.SaveAs(oExcel.Path & ".xls")
End Sub

      

Thank you so much

+3


source to share


2 answers


I think Inafiziger solved your main problem, it must be vanilla Save

.

As it was not clear to me what you are doing (i.e. Visual Studio / VB / BA), then

Incl. (1)

I thought it worth clarifying that you can use the code inside the module ThisWorkbook

to detect and process SaveAs

if you give users a choice. This event detects SaveAs

and cancels it

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then
        MsgBox "You cannot use SaveAs to save this file", , "Save Cancelled!"
        Cancel = True
    End If
End Sub

      



This code could be programmatically added to your target book, but I doubt you would need to resort to this so you can run a simple one Save

.

Incl. (3)

You need to use Early Binding to get the edge intellisense

. You are currently using the last binding with oExcel = CreateObject("Excel.Application")

. The commonly used approach is to write the code and make it work with early binding, and then convert it to late binding for the final publication of the code.

Conditional compilation (see comment below) can be used to switch between two binding methods in the same code.

+4


source


You must save the book. for example oBook.Save

.



If you create a new file, you will need to use SaveAs

with a valid file name in order to save it the first time.

+2


source







All Articles