• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

Need to Open Excel in VB Form and then save it on a network drive

Hello Experts,

I need to open an existing excel file in my VB application. Then when the user hits the save button it gets saved in a predefined network drive and folder.Not via OLE.

ThanX for any inputs. Source code will be highly appreciated.

Email : vineetc@usa.com

1 Solution
Sure thing. You'll have to include the Microsoft Excel Object Library in your application, first; do that from the Tools | References menu. Then, here is sample Form code to do what you want. (This was written against Excel XP/10/2002; I think it'll work on other versions, but may need tweaking.)

Option Explicit

Private myApp As Excel.Application
Private WithEvents myDoc As Excel.Workbook

Private Sub Form_Load()

    Set myApp = New Excel.Application
    Set myDoc = myApp.Workbooks.Add
    myApp.Visible = True

End Sub

Private Sub myDoc_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Static BeforeSaveRunning As Boolean ' prevent recursion
    If Not BeforeSaveRunning Then
        BeforeSaveRunning = True
        Cancel = True
        myDoc.SaveAs "c:\temp\myfile.xls"
        BeforeSaveRunning = False
    End If

End Sub

Simple and Plain:

Add Excel 9.0 Reference to project: (Click Project; References; and find EXCEL 9.0 object library)

Declare this on General:
dim xlsapp as excel.application

Add Command Button to form

Set xlsapp = Excel.Application
   With xlsapp
        'Show Excel
        .Visible = True
        'Create a new workbook
        .Workbooks.Open "C:\Spread.xls"   'Workbook you want to open
        .ActiveWorkbook.SaveAs "c:\Spread.xls"   'Where to save
   End With

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now