export access data to specific cell placement

I know you can import from excel from specific cells using VBA I do it all the time.
However I need to export data to excel to specific cells.
1: Dates to cell B2 and E2
then table info to
2: A5:AH2000
I have always been told you cannot export to specific cells is this the case?
Stephen RoesnerAnalysisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
<I have always been told you cannot export to specific cells is this the case? >
not really..
using recordset you can use the command "copyfromrecordset"

something like this

dim rs as dao.recordset
set rs=currentdb.openrecordset("tableName or query")

xlObj.ActiveSheet.Range("A5").copyfromrecordset rs

is the excel file to export the records already exists?

upload a copy of the db and the Excel file
0
Dale FyeCommented:
You can use Excel automation to open Excel, select a worksheet, and write the data to your worksheet using the CopyFromRecordset method.

sent from my iPhone or I would provide more details and an example.
0
Stephen RoesnerAnalysisAuthor Commented:
Rey
I used your code and stepped thru it

dim rs as dao.recordset
 
set rs=currentdb.openrecordset("Vista_Matches")  -Vista matches is a table of the results
 xlObj.ActiveSheet.Range("A5").copyfromrecordset rs
when it gets to this line it fails requesting an object (Runtime 424 Object required)

am I missing something?
do I need to open the spreadsheet first
I do need to go to a specific tab ("01_Matching_Records")

I was doing a docmd.transferspreadsheet acexport after that line
but im assuming I don't need that anymore
any thoughts/help
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
here is the basic codes


Dim xlObj As Object, rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Vista_Matches")
    Set xlObj = CreateObject("excel.application")
        xlObj.workbooks.Add
    xlObj.ActiveSheet.Range("A5").copyfromrecordset rs
   
    xlObj.Quit


post back the result
0
Dale FyeCommented:
you must first instantiate an Excel Object (xlObj).  Code for this looks like:

Dim xlObj as Object  ' late binding  Excel.Application
Dim wbk as Object    ' late binding  Excel.Workbook

On Error Resume Next
set xlObj = GetObject(, "Excel.Application")
if Err.Number <> 0 then set xlObj = CreateObject("Excel.Application")

'Either of these will work
'If you want to set the workbook to  the active workbook
set wbk = xlObj.ActiveWorkbook    'This will fail if there is no active workbook
'If you want a new workbook.
set wbk = xlObj.Workbooks.Add
'If you want to insert into an existing workbook that you know the name of
set wbk = xlObj.Workbooks.Open(strFilename, False)

After you write your data to the spreadsheet, you must then release your objects.
set wbk = nothing
set xlObj = nothing
0
Stephen RoesnerAnalysisAuthor Commented:
Dale / Rey  I am using what I believe is a combo of your code and it amazingly seems to be somewhat working. Hopefully you can fine tune it for me. Here is my code:
Dim fd As String
Dim rs As DAO.Recordset
Dim xltemp As Excel.Application
Dim xlObj As Object
Dim wbk As Object

DoCmd.SetWarnings False
DoCmd.Hourglass True

fd = Format(Now, "yyyymmddhhnnss")

DoCmd.OpenQuery "Vista_Master Matching HS_Master"
DoCmd.OpenQuery "Vista_Master WO Matching HS_Master"
DoCmd.OpenQuery "Vista Master Duplicates"

Set rs = CurrentDb.OpenRecordset("Vista_Matches")
On Error Resume Next
Set xlObj = GetObject(, "Excel.Application")
Set wbk = xlObj.Workbooks.Open("C:\ERPDP\Membership\FileOut\Membership_Compare_Report.xls", True)
If Err.Number <> 0 Then Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add
    xlObj.ActiveSheet.Range("A5").CopyFromRecordset rs
     xlObj.Quit
Set wbk = Nothing
Set xlObj = Nothing

DoCmd.SetWarnings True
DoCmd.Hourglass False

  however it is sending the data to a created excel sheet named "book1" instead of going to the template C:\ERPDP\Membership\FileOut\Membership_Compare_Report.xls to the specific tab of "01_Matching_Records" any thoughts on what im doing wrong I do see that im not sending it to the specific tab how is that done. Sorry never did this stuff before going directly into excel. This will be a first with this kind of code.
0
Rey Obrero (Capricorn1)Commented:
@Grizbear51
it is very important the you respond to the question asks by experts, like this one i asked on my first posts
             

is the excel file to export the records already exists?




try this codes


Dim fd As String
Dim rs As DAO.Recordset
Dim xlObj As Object


DoCmd.SetWarnings False
DoCmd.Hourglass True

fd = Format(Now, "yyyymmddhhnnss")

DoCmd.OpenQuery "Vista_Master Matching HS_Master"
DoCmd.OpenQuery "Vista_Master WO Matching HS_Master"
DoCmd.OpenQuery "Vista Master Duplicates"

Set rs = CurrentDb.OpenRecordset("Vista_Matches")

Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open "C:\ERPDP\Membership\FileOut\Membership_Compare_Report.xls"
	xlObj.Worksheets("01_Matching_Records").Activate
    xlObj.ActiveSheet.Range("A5").CopyFromRecordset rs
	xlObj.ActiveWorkbook.Save
    xlObj.Quit

Set xlObj = Nothing

DoCmd.SetWarnings True
DoCmd.Hourglass False

Open in new window

0
Helen FeddemaCommented:
Here is some code, similar to the other examples but maybe a little different approach.
   Dim appExcel As New Excel.Application
   Dim cnn As ADODB.Connection
   Dim wkb As Excel.Workbook
   Dim sht As Excel.Worksheet
   Dim strWorkbook As String
   Dim strRange As String
   Dim lngLastRow As Long
   Dim rst As ADODB.Recordset
   Dim rng As Excel.Range
   Dim strWorkbookName As String
   Dim strDefault As String
   
   DoCmd.SetWarnings False
   strPrompt = "Enter workbook name (no extension)"
   strTitle = "Workbook name"
   strDefault = "New Access Data"
   strWorkbookName = InputBox(strPrompt, strTitle, strDefault)
   
   'Run make-table queries
   DoCmd.OpenQuery "qmakCAContacts"
   
   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   
   'Create a recordset based on a select query.
   rst.Open Source:="qryContacts", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
      
   'Export query
   Set wkb = appExcel.Workbooks.Add
   appExcel.Visible = True
   strWorkbook = Application.CurrentProject.Path & "\" & strWorkbookName
   wkb.SaveAs FileName:=strWorkbook
   Set sht = wkb.Sheets(1)
   strRange = "A1"
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close

Open in new window

0
Stephen RoesnerAnalysisAuthor Commented:
Rey
when I run your code it fails on
xlObj.Worksheets("01_Matching_Records").Activate
it says run time error '9'
subscript out of range

any ideas?
I made sure the tab is the correct name - even copied and pasted it in the code.
0
Rey Obrero (Capricorn1)Commented:
try changing

xlObj.Worksheets("01_Matching_Records").Activate

with

xlObj.Sheets("01_Matching_Records").Activate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen RoesnerAnalysisAuthor Commented:
Rey That worked perfect thatnk you so much for your help I really appreciate it
0
Stephen RoesnerAnalysisAuthor Commented:
Thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.