VBA code in Access 2000 open Excel as Read Only

[Inquisitive1]

I am having fun trying to incorporate VBA code with my assignments. I
have run into a problem opening an Excel file from Access 2000 VBA -
the Excel file keeps opening as "Read-Only". My goal is to open the
Excel file and change the value of a cell to the value of a variable in
code (is that even possible, or can cells in Excel only be updated with
recordsets?). Here is my code so far...

Public Function ScorecardCreateDate()
On Error GoTo Err_ScorecardCreateDate

Dim strFile As String
Dim ScorecardFileDate As Date
Dim NMLPPMFileDate As Date
Dim NMLFileDate As Date

strFile = "C:\Metrics\Scorecards\ManufacturingOnlyScores.xls"
ScorecardFileDate = FileDateTime(strFile)

strFile = "C:\Metrics\NewModelLaunch\NML-PartLevelRisk.xls"
NMLFileDate = FileDateTime(strFile)

strFile = "C:\Metrics\Ppm\CYTD PPM\Exception
Reports\NML_CYTDPPM.xls"
NMLPPMFileDate = FileDateTime(strFile)

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strFileName As String

strFileName = "C:\Metrics\Ppm\CYTD PPM\Exception
Reports\NML_CYTDPPM.xls"
Set xlBook = xlApp.Workbooks.Open(strFileName, , False, , , False,
True)
xlApp.Application.Visible = True
Set xlSheet = xlBook.Worksheets("ReportDates")
xlSheet.Activate
xlSheet.Range("B1").Value = NMLFileDate


Set xlApp = Nothing
Set xlBook = Nothing

Exit_ScorecardCreateDate:
Exit Function

Err_ScorecardCreateDate:
MsgBox Err.Description
Resume Exit_ScorecardCreateDate
End Function

 

[Gman]

This is maybe more of a question for the XL NGs
(microsoft.public.excel.programming) however... there's nothing wrong
with your code that I can see.

Are you sure

(a) someone else doesn't have the workbook already open (if that folder
is shared)?
(Yourself included, maybe in a different instance of Excel - possibly
one that is not visible - check in Task Manager for additional instances
of Excel that may be running invisibly.)

(b) the file's attributes are not set as ReadOnly (check in Explorer,
right click and look at the properties).

(c) Unlikely... you have write rights to the folder - if you have NTFS
as your disk file system this could have been set to read only.

(d) A lock hasn't inadvertently been left on the file maybe by the file
being opened and then your code crashing during an earlier attempt. (I
would reboot your machine to be sure.)

HTH

 

[Inquisitive1]

GMan,

Thanks!! Can you believe that I rebooted and the code worked like a
charm? Well, I did have to add some lines to save and close the file.
But, how could I have forgotten about my old days as Help Desk support
to tell people to reboot and try again?!

Thanks!!

 

[Gman]

Heh heh. Never fails eh?