Code for Starting Excel

[Phil]

I am using Office 2000 and from the Switchboard in Access,
I want to start an Excel workbook named Registration and
have it open a worksheet called Current Month. Can anyone
help with the code to do this from Access?

Thanks in advance.

 

[Ken Snell]

Here's some sample code for opening an EXCEL workbook:

Public Sub TestMacroRun()
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
' put code here to write into the cells etc.
' .
' .
' .
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
End Sub

 

[Mike Tanner]

I have tried this code nad it works fine except that the Excel Workbook
opens in "read only" mode. Is there a way to set the property to allow
editing and saving in the workbook?

Thanks

 

[Ken Snell]

The code that I posted will open the workbook in Edit mode. The third
argument of the Workbooks.Open action is the ReadOnly value: if set to
True, then it opens as ReadOnly. If left blank or set to False, then the
book opens in Edit Mode.

Does the workbook that you're opening have its ReadOnly attribute set to
True? (right-click the file icon in Windows Explorer and see if the Read
Only checkbox is checked).

 

[Mike]

Thanks for the info. I checked the properties of the file and confirmed that
it is not set to "read only". Here is the sample code I am using for testing
purposes. I am greatly interested in getting this code to work because I
have different excel files located in directories and some of the directory
names contain spaces and the "Shell" function does not deal well with the
spaces in the directory names. I am passing variables to the directory
location based on the user input, that is why I declared the file location
as a string. Thanks in advance for your help.

Private Sub Command0_Click()

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim strLoc As String
strLoc = "C:\Downloads\excel test\book1.xls"
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open(strLoc)
Set xls = xlw.Worksheets("test2")
Set xlc = xls.Range("A1")
' put code here to write into the cells etc.
' .
' .
' .
'Set xlc = Nothing
'Set xls = Nothing
'xlw.Save
'xlw.Close False
'Set xlw = Nothing
'xlx.Quit
'Set xlx = Nothing
End Sub

 

[Ken Snell]

The code that you've posted should open the workbook in an Edit mode. I am
not seeing anything there that would cause ReadOnly unless you don't have
permission for writing onto the C: drive. Or unless the workbook file is
already opened when you try to open it. By any chance, have you run the code
the way you've posted it; without the "...Close" and "Set ... = Nothing"
statements being executed? You may have an instance of EXCEL running that
has the workbook open.

Try opening the file manually; does it indicate that it's already open?

Otherwise, I don't know what else to suggest that you look at. This should
work.

 

[Ken Snell]

Another possible reason for "read only"....is the file protected with a
"write" password?

 

[Mike]

Thanks Ken, I did have an instance of Excel running that did not show up
under the Task Manager. I rebooted and this cleared the problem. I
discovered this by trying to delete the file. The script works fine now so
thanks again.

 

[Ken Snell]

Glad you found the problem. Be sure to include the "Set ... = Nothing" etc.
parts in the code so that the problem won't recur.

Good luck.