Excel Opens in Read Only

[Skip Bisconer]

I am using this code to write to an .xlsx file and when it opens it opens in ReaD Only. Can some one give me a cure for this?

Function ExportToExcel()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Amex\Bisconer.xlsx")
Set xlWS = xlWB.Worksheets("CurrentCharges11152")
 
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("LastName").Value
  .Range("B" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("C" & i + 1).Value = rs.Fields("CardNumber").Value
  .Range("D" & i + 1).Value = rs.Fields("Date").Value
  .Range("E" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("F" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("G" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("H" & i + 1).Value = rs.Fields("Amount").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessPurpose").Value
  .Range("J" & i + 1).Value = rs.Fields("Customer").Value
  
  
  End With
i = i + 1
rs.MoveNext
Loop
End Function

 

[boblarson]

You're sure that the Excel file is not set to read-only in its file properties?

 

[Skip Bisconer]

Hi Bob,

I don't think so. I am testing out a process where I export 11 different XL spreadsheets that are sent email to recipients to enter their data and when returned copied back down to the original file which is linked to the Access program to admiister credit card charges.

The read only issue has cropped up twice with different files and when I delete the file and re export it it opens as it should in a Write state. I just thought there might be something in my code that is causing it.

At first I thought it was just opening another instance of XL but after some more trials I haven't been able to repeat it. Can you tell me if XL is already open, and with this process, would the code produce another instance of XL?

 

[chaostheory]

Not sure if this will help but i use excel to autoupdate 4 different excel files that are read only except during updating. If you are having a problem where it sometimes is getting set to read only might try putting in just above the open statement to always make sure the file itself is set to write mode regardless of what it may have been switched too. Kind of a workaround if it works in access.

SetAttr "C:\Amex\Bisconer.xlsx", vbNormal

Works in excel never tried in access though.

 

[Skip Bisconer]

I found my problem. While testing this procedure I encounter an error. This seem to leave the file open and when rerunning the process the file was considered as already open. When I did Ctrl/Alt/Del I had three instances of Excel open but non visible.
Thanks for your input.