Access Variable to Excel

  • Thread starter Thread starter Bob Barnes
  • Start date Start date
B

Bob Barnes

"SteveT" was kind enough to give me code to transfer Access
Recordset data to Ranges in Excel. . .no problem.

It uses...(essentially)..

Set RS = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
. . .
objXLSheet.Range(strCellRef).CopyFromRecordset RS

I have a label's caption which I Dim Y As a String.
Trying to use the "CopyFromRecordset" Excel method
doesn't work. I know very little Excel.

How would I get this "Y string" to. . .
objXLSheet.Range(BobName).....

TIA - Bob
 
objXLSheet.Range("A1").Value = strMyString

or

objXLSheet.Cells(rownr, colnr)= Me.lblMyLabel.Caption


hth
 
Waldo - Thank you. Wasn't here yesterday, but just tried
the 1st one & it failed (will try the 2nd code). Failed
here . .
.....
objXLSheet.Range(TheDate).Value = Y --> To
Error 1004 below
objXLSheet.Range(sAllScrap).CopyFromRecordset RU
objXLSheet.Columns.AutoFit
'Save wb
objXLWb.Save
objXLWb.Close
'close up other rs objects
If Not RS Is Nothing Then RS.Close
Set RS = Nothing
Set objXLSheet = Nothing
Set objXLWb = Nothing
'quit Excel
If Not objXLApp Is Nothing Then objXLApp.Quit
Set objXLApp = Nothing
DoCmd.Hourglass False
Exit Sub
ProcError:
Select Case Err
Case 9 'Worksheet doesn't exist
objXLWb.Worksheets.Add
Set objXLSheet = objXLWb.ActiveSheet
objXLSheet.Name = strWorkSheet
Resume Next
Case 1004 'Workbook doesn't exist, make it
objXLApp.Workbooks.Add
Set objXLWb = objXLApp.ActiveWorkbook
objXLWb.SaveAs strWorkBook
Resume Next

Will be in touch - Bob
 
Waldo - Also tried. . .

objXLSheet.Range(TheDate).Value = _
Forms![Main Switchboard]!GoDate.Caption

It also failed. I did not try the "Cells(rownr, colnr)"
has wouldn't that be a hard-code. Should be better to use
a Named Range ??

Bob
 
Back
Top