Access export to specific excel cells

I have always used code to export accessto excel with no issues using this code.
      DoCmd.TransferSpreadsheet acExport, 8, "Structure Detail", sOutputa, True

However, now I need to export data to specific cells within a template.
I am trying to put header data to tab1 b1:b9
and put the detail data to tab1 A11:X100
X100 being arbitrary just and end point

I see that when I go thru the code it allows for a range but I can't get to work.
I have tried several versions of the last part:
DoCmd.TransferSpreadsheet acExport, 8, "Structure Detail", sOutputa, False, ["A11:X100"]
DoCmd.TransferSpreadsheet acExport, 8, "Structure Detail", sOutputa, False, ["A11"]
DoCmd.TransferSpreadsheet acExport, 8, "Structure Detail", sOutputa, False, "A11:X100"

is it not possible or am I doing the last part wrong
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't define a range when exporting. See this bit of information from http://msdn.microsoft.com/en-us/library/office/ff844793(v=office.15).aspx, regarding the Range argument:
A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
The only way I know of to export to specific cells in Excel is to use automation.
0
Stephen RoesnerAnalysisAuthor Commented:
can you point me in the direction of automation - an example out on the net or something  that I can see how its done?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Here's a good starting point:

http://support.microsoft.com/kb/219151

Note that this uses "Early Binding", which means you must set a reference to Excel. If you plan on distributing the app to others, this may cause issues, so often we'll develop with Early Binding (i.e. with the Reference to Excel set) and then switch over to "Late Binding". Basically, Late Binding means we remove the reference, and change the way we work with the library. Instead of writing this:

Dim xl As Excel.Application

We would do this:

Dim xl As Object
Set xl = CreateObject("Excel.Application")

This would use whatever version of Excel is on the end user machine, and would not tie your application to a specific version. There are some downsides to this method - for example, you must be sure to NOT use constants and such that are specific to the host library, you don't get Intellisense when working in VBA, etc - but overall the pluses outweigh the minuses.
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.

Stephen RoesnerAnalysisAuthor Commented:
OK I kinda switched gears
it hit me last nite that I could just build a macro in excel
and then open and run the macro to do my bidding.
However I'm having a problem with getting the macro to run.
Here's the code I'm using in access:
It opens the file and the data is there and I can manually run the macro and it all works
but it wont run the macro from access any thoughts?

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim fd As Long
Dim DirName As String
 
DoCmd.SetWarnings False
DoCmd.Hourglass True
 
fd = Format(Date, "yyyymmdd")
 
sTemplateA = CurrentProject.Path & "\Templates\Benefit Config Acct Set Up Template.xls"
sOutputa = CurrentProject.Path & "\FileOut\Benefit Config Acct Set Up " & fd & ".xls"
If Dir(sOutputa) <> "" Then Kill sOutputa
FileCopy sTemplateA, sOutputa
 
DoCmd.TransferSpreadsheet acExport, 8, "Structure", sOutputa, False
DoCmd.TransferSpreadsheet acExport, 8, "RX_Benefits", sOutputa, False
DoCmd.TransferSpreadsheet acExport, 8, "Residence States for PDP", sOutputa, False
 
Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
    Set xlWB = .Workbooks.Open(sOutputa)
            xlWB.Run "Transfer_Data"     (This is the macro)
    End With
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't have any idea why it wouldn't run. You'd have to ask that of the Excel experts ...
0
Helen FeddemaCommented:
Here is some code that writes Access data to specific Excel cells (from my Working with Excel ebook sample database):
Public Sub CreateInspectionReport(lngVehicleID As Long)
'Created by Helen Feddema 11-Jun-2010
'Last modified by Helen Feddema 9-Oct-2011

On Error GoTo ErrorHandler

   Dim appExcel As New Excel.Application
   
   strRecordSource = "tblVehicles"
   strQuery = "qrySelectedVehicle"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[VehicleID] = " & lngVehicleID & ";"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      strPrompt = "No records found; canceling"
      strTitle = "Canceling"
      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
      GoTo ErrorHandlerExit
   Else
      Set rst = CurrentDb.OpenRecordset(strQuery)
   End If
   
   'Create new workbook from template
   strDocsPath = GetProperty("DocumentsPath", "")
   strTemplatesPath = GetProperty("TemplatesPath", "")
   strTemplate = strTemplatesPath & "\Northwind Inspection Report.xltm"
   Debug.Print "Template: " & strTemplate
   Set wkb = appExcel.Workbooks.Add(template:=strTemplate)
   Set sht = wkb.Sheets(1)
   appExcel.Visible = True
   
   'Write data for selected vehicle to cells of worksheet
   rst.Edit
   sht.Range("A5").Value = rst![Appraiser]
   sht.Range("C5").Value = rst![ClaimNumber]
   sht.Range("E5").Value = Format(rst![ClaimDate], "mmm d, yyyy")
   sht.Range("A7").Value = rst![Inspector]
   sht.Range("B7").Value = rst![Location]
   sht.Range("D7").Value = rst![YearMakeModel]
   sht.Range("G7").Value = rst![AppraiserRate]
   sht.Range("H7").Value = rst![InspectorRate]
   sht.Range("A9").Value = Format(rst![InspectionDate], "dd-mmm-yyyy")
   sht.Range("B9").Value = Format(rst![CompDate], "dd-mmm-yyyy")
   sht.Range("C9").Value = rst![VIN]
   sht.Range("D9").Value = rst![Mileage]
   sht.Range("E9").Value = rst![Plate]
   sht.Range("F9").Value = rst![State]
   sht.Range("G9").Value = rst![LaborTax]
   sht.Range("H9").Value = rst![PartsTax]
   rst![ReportSent] = Date
   rst.Update
   
   'Protect and save filled-in workbook
   sht.Protect DrawingObjects:=True, _
     Contents:=True, _
     Scenarios:=True
   sht.EnableSelection = xlUnlockedCells
   sht.Range("A13").Select
   
   strSaveName = strDocsPath & "\Preliminary Vehicle Inspection Report for " _
      & rst![YearMakeModel] & ".xlsm"
   Debug.Print "Save name: " & strSaveName
   wkb.SaveAs FileName:=strSaveName, _
      FileFormat:=xlOpenXMLWorkbookMacroEnabled
   
   strTitle = "Export successful"
   strPrompt = strSaveName & " created"
   MsgBox prompt:=strPrompt, _
      Buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle
   
ErrorHandlerExit:
   Set appExcel = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CreateInspectionReport procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

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:
Thank you works great
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.