• 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

-VN69
0
vn69
Asked:
vn69
1 Solution
 
graham_charlesCommented:
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


g.
0
 
reinhardt007Commented:
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

Command1_Click()
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
        .ActiveWorkbook.Close
   End With
       
       
       
0

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