Value won't pass to spreadsheet from Access form

[lgbatdw]

I need to pass the user's defined date range from an access form to an Excel spreadsheet. The spreadsheet is a pivot table based on an external data source (a dynamically built Access 2003 table) Everything seems to be working properly except the dates from the access form are not getting into the spreadsheet. I thought this worked at initially, but maybe it never did (it's in testing). Anyhow, this is my code. Seems like this should be easy, but I just can't figure it out. What am I missing?


'initiate Excel & open (pivot table) workbook
Dim XL As Object, WB As Object
Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Open(strPathAndFile)
XL.Application.Visible = True

'pass dates selected from access form to excel worksheet
WB.Sheets(1).Range("a2").Value = "Selected Date Range: " & CDate(Me!StartDate) & " through " & CDate(Me!EndDate)
XL.Run "RefreshData"


Linda in MN

 

[lgbatdw]

I see what's happening - the value I'm passing is getting to sheet 2 somehow. correct cell, wrong sheet. If I delete sheet 2, then it works - if I add sheet 2 then the info is passed to the correct cell, but wrong sheet. Sheet 1 (as shown on bottom tab) is the one it's supposed to go to. Am I referencing the wrong sheet somehow?

Linda in MN

 

[lgbatdw]

OK, I finally figured it out. The spreadsheet tabs were in reverse order (Sheet 2 / Sheet 1) so I dragged the tab for sheet one over and hooray - it works again. I don't have much Excel experience and have no idea how that happened. Anyhow it's working!

Linda in MN

 

[PHV]

And what about something like this ?
WB.Sheets([!]"NameOfSheet"[/!]).Range("a2").Value = ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886