Open an Existing or New Excel Workbook

[dching]

I have a database that contains two fields:

Excel file name
File location

I'm trying to create a click button on a form that when clicked, it uses the [file location] and [excel file name] to launch excel and open the existing workbook ([excel file name]) at the file location ([file location]).

However, I am unable to find the VB script that would execute this action.

In addition to this, I would also like for the script to test to see if the excel file exists at the defined location. If not, I would like to have excel launch and create a blank workbook with the file name stated in the control [excel file name] at the defined location [file location].

Finally, would the script work if I have field names with spaces (as shown above)?

Can anyone help?

Thanks in advance.

 

[PearlGI]

The following code should do what you're asking.

Replace the FileLocation and ExcelFileName variables in this code with the references to your form controls.

You also need to ensure you reference the Microsoft Excel Object Library. To do this go into any module, select Tools, References and select it from the list.

------------------------
Public objExcel As Excel.Application

'If Excel is already open Err.Number returns zero otherwise
'a new Excel session is created
Sub OpenExcel()

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0

'The next 3 lines give Excel focus
objExcel.Visible = True
objExcel.WindowState = xlMinimized
objExcel.WindowState = vbMaximizedFocus

If Dir(FileLocation & ExcelFileName) = "" Then
'If file doesn't exist, add new workbook and save
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs FileLocation & ExcelFileName
Else
'if file does exist open it
objExcel.Workbooks.Open FileLocation & ExcelFileName
End If
objExcel.ScreenUpdating = True
End Sub
-----------------

 

[dching]

Thank you so much for the code. It works great. I've placed it in my library for future use.

Thanks again.

Derek

 

[korn]

open excel via asp

hi
I'm very new at vbscript programming.I would to do the same thing as you did(to open excel file via asp+vbscript).I had read your mail post to PearlGI and i learn that you success in doing so.I would appreciate if you could send the complete code asp+vbscript just to open,show, and close excel file on IE browser.I belived that you are using VB to implement it.Is there any way to just use asp+vbscript only to do the same thing.If so kindly convert code for me please from vb to asp+vbscript.

Thank you very much
Regard,
Korn
korn@marqwell.com

 

[MicroShaft]

hi

i have a Access db which is opening a workbook which is stored in the same folder.

how do a reference the the location of the file if at a later date the folder may be transfered to another location .

currently the locaction is Desktop\Dbfolder\dbname.mdb

if this folder is to be moved to lets say just on the C:

how would i reference in the location so it only looks in the folder

 

[PearlGI]

CurrentDB.Name

 

[PearlGI]

see this post if you want to know how to extract just the folder name rather than with the filename as well.

http://www.access-programmers.co.uk/forums/showthread.php?t=60268&

 

[MicroShaft]

see this post if you want to know how to extract just the folder name rather than with the filename as well.

http://www.access-programmers.co.uk/forums/showthread.php?t=60268&

Hi

Can you give me some help on determining if a workbook is open or closed.

I want to do a check in a function in MS ACCESS to make sure a workbook is closed

cheers