Opening Excel files from Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to use VBA to open a protected Excel File.
Can't seem to get the arguements correct.
Any ideas?
Here is my code -

Sub UnProtectFile(ByVal sFilename)
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
'Open the file
Set xlBook = xlApp.Workbooks.Open(sFilename, , False, , "xxxyyy", False,
True)
'Set the file for read only
xlApp.Application.DisplayAlerts = False
xlBook.SaveAs sFilename, , , , False
xlApp.Application.DisplayAlerts = True

xlBook.Close True
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

End Sub
 
Something like this may work for you.

Public Sub Open_CRoomShift()
Dim xlApp As Variant
Dim xlBook As Variant
Dim ver As Integer
ver = ExcelVersion
Set xlApp = CreateObject("Excel.Application." & ver & "")
Set xlBook = xlApp.Workbooks.Open("C:\data\excel\CRoomShift.xlt")
xlApp.Application.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
End Sub

Public Function ExcelVersion()
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
Select Case WshShell.RegRead("HKCR\Excel.Application\CurVer\")
Case "Excel.Application.8"
ExcelVersion = 8
Case "Excel.Application.9"
ExcelVersion = 9
Case "Excel.Application.10"
ExcelVersion = 10
Case "Excel.Application.11"
ExcelVersion = 11
End Select

End Function

I used XLT since an excel template cannot be saved back to itself.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I would like to use VBA to open a protected Excel File.
| Can't seem to get the arguements correct.
| Any ideas?
| Here is my code -
|
| Sub UnProtectFile(ByVal sFilename)
| Dim xlApp As New Excel.Application
| Dim xlBook As Excel.Workbook
| 'Open the file
| Set xlBook = xlApp.Workbooks.Open(sFilename, , False, , "xxxyyy",
False,
| True)
| 'Set the file for read only
| xlApp.Application.DisplayAlerts = False
| xlBook.SaveAs sFilename, , , , False
| xlApp.Application.DisplayAlerts = True
|
| xlBook.Close True
| xlApp.Quit
|
| Set xlBook = Nothing
| Set xlApp = Nothing
|
| End Sub
|
 
Back
Top