Can't send to worksheet(2) from Access (1 Viewer)

[DenMiller57]

I cannot send to the second worksheet in Ex. The following code works great sending to sheet 1, but I get an undefined error with page 2. I have tried to use the activate method before posting. Here is the code that works. Im DOA, is that the problem?
Dim DB As Database
Dim rst As Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWsMel As Object
Dim xlWsERD As Object
Set DB = CurrentDb
Set rst = DB.OpenRecordset("tmp_ExportExpenses")
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(ExcelExpensePath)
Set xlWsMel = xlWb.WorkSheets(1)
Set xlWsERD = xlWb.WorkSheets(2)
Set xlWsRCPT = xlWb.WorkSheets("RCPT")

' These 3 lines work great
xlWsMel.Cells(1, "C").Value = rst!Fullname
xlWsMel.Cells(1, "G").Value = rst!EABN
xlWsMel.Cells(1, "I").Value = ProcessDate

xlWsERD.Activate
' The next 3 regardless of order or value after the equal sign, do not work
xlWsERD.Cells(4, "K") = rst!Fund
xlWsERD.Cells(4, "O").Value = rst!AgreementNum
xlWsERD.Cells(6, "N").Value = "Training Division"
xlWb.Save
xlWb.Close
xlApp.Quit

' Close objects
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
' Release Excel references
Set xlWsMel = Nothing
Set xlWsERD = Nothing
Set xlWsRCPT = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

 

[DenMiller57]

Have also tried using an ADO recordset with the following code, same results, posts to Sheet 1 but not sheet2. I think the problem is in the Excel code, but I'm very new to using Excel specific code. Here is ADO code, if it helps

Dim rst As New ADODB.Recordset
Dim strDB As String
strDB = "C:\MyDatabase.mdb"

Set rst = New ADODB.Recordset
rst.Open "Select * From Tmp_ExportExpenses", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

The rest of it is about the same. Thanks for any suggestions.

 

[DenMiller57]

Final note. The problem is not with the code, but with something in the formatted destination SS. The code post fine to a blank SS.
Dennis

 

[fuzzygeek]

Are the spreadsheets named? Are the cells formatted so that nothing can be written to them?

 

[DenMiller57]

I dont know if the cells are formatted so nothing can be written to them. What I have found is: I can post to the third WS using code. I can also type text into the cells of the second ws which wont accept text by code. I have also found I can auto retrieve data into the second WS if I post it to the first WS. So unless I figure out how to get the second WS to accept posts from code, I am going to look into creating a fourth hidden WS, then post the data I want in the second WS to the fourth WS and auto retrieve it back into the second WS. A lot of hassle, but I need to use the formatted WS. Cant get arorund that requirement. Thanks for asking.
Dennis

 

[JimmyK]

Set xlWsERD = xlWb.WorkSheets(2)

xlWsERD.Activate
' The next 3 regardless of order or value after the equal sign, do not work
xlWsERD.Cells(4, "K") = rst!Fund
xlWsERD.Cells(4, "O").Value = rst!AgreementNum
xlWsERD.Cells(6, "N").Value = "Training Division"
xlWb.Save
xlWb.Close
xlApp.Quit

Have you tried adding .value after xlWsERD.Cells(4, "K") to see if that solves the problem? If not, try removing the rst!Fund, etc and put in text to see if it writes to it.

 

[DenMiller57]

Thanks for replying.
I am using sheet indices not names, such as
Set xlWsMel = xlWb.Worksheets(1)
Set xlWsERD = xlWb.Worksheets(2)
Set xlWsRCPT = xlWb.Worksheets(3)
I dont think the cells have any locks or conditional formatting. The Sheet is not locked, or doesnt show its locked. The cells I am trying to ref do not have any conditional formatting and do not appear locked. I can type formulas in them, (although the formulas dont work)
I have changed the command line to use Range:
xlWsERD.Range("K4").Value = rst!Fund
no change. I have also tried to create a 4th Sheet, send the data to the 4th sheet and then write code to pull the data back into the second sheet. The data goes to the 4th sheet correctly, but the code on sheet 2 wont pull it back. I can write to sheets 1,3,and 4 just not 2.
When I try to write code in sheet 2, it shows the formula in the cell even when the cell is not selected and that really has me stumpted. Here is the formula that works in cells on other sheets, but not sheet 2.
=IF(Misc!J2>0,Misc!J2," ")
Normally the formula does not display unless the cell is selected, not on sheet 2, all the formulas are always visible in the cells.
My problem is, while I am really comfortable with Access, I am a novice when it comes to excel. Thanks for any ideas.