Access export to specific excel cells

Posted on 2014-08-21
Last Modified: 2014-08-25
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
Question by:Grizbear51
    LVL 84
    You can't define a range when exporting. See this bit of information from, 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.

    Author Comment

    can you point me in the direction of automation - an example out on the net or something  that I can see how its done?
    LVL 84
    Here's a good starting point:

    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.

    Author Comment

    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
    LVL 84
    I don't have any idea why it wouldn't run. You'd have to ask that of the Excel experts ...
    LVL 31

    Accepted Solution

    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
          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
       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
       'Protect and save filled-in workbook
       sht.Protect DrawingObjects:=True, _
         Contents:=True, _
       sht.EnableSelection = xlUnlockedCells
       strSaveName = strDocsPath & "\Preliminary Vehicle Inspection Report for " _
          & rst![YearMakeModel] & ".xlsm"
       Debug.Print "Save name: " & strSaveName
       wkb.SaveAs FileName:=strSaveName, _
       strTitle = "Export successful"
       strPrompt = strSaveName & " created"
       MsgBox prompt:=strPrompt, _
          Buttons:=vbInformation + vbOKOnly, _
       Set appExcel = Nothing
       Exit Sub
       MsgBox "Error No: " & Err.Number _
          & " in CreateInspectionReport procedure; " _
          & "Description: " & Err.Description
       Resume ErrorHandlerExit
    End Sub

    Open in new window


    Author Closing Comment

    Thank you works great

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now