[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1837
  • Last Modified:

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
0
Stephen Roesner
Asked:
Stephen Roesner
  • 3
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Stephen RoesnerAnalysisAuthor Commented:
Thank you works great
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now