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

Posted on 2003-03-10
Medium Priority
Last Modified: 2010-05-01
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

Question by:vn69
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

graham_charles earned 60 total points
ID: 8105427
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


Expert Comment

ID: 8111927
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question