Solved

Writing record to Excel

Posted on 2011-03-25
6
401 Views
Last Modified: 2013-11-27
I have a table with a column of month end dates.  I also have an .xls file for each of those dates.  Those files are right now identical except for the file name which is based on the month end date, i.e., R1000_20101231.xls.   I need to write the date into a single cell on each excel spreadsheet.  I have used DoCmd.TransferSpreadsheet acExport before to take an entire query and write it to a spreadsheet but is there a way to write a single record to a single excel cell, i.e., cell A2?

Thanks for any help!
0
Comment
Question by:kobys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35217692
see the methods from this link

Using Automation to Transfer Data to Microsoft Excel
http://support.microsoft.com/?kbid=210288
0
 

Author Comment

by:kobys
ID: 35218022
Capricorn1,

Thanks for the reference!   I'm getting a "Subscript out of range" error at the line: mysheet.Application.windows("Y:\Susan and Rob\Equities\R1000\R1000_" & TxtDate & ".xls").Visible = True

 
Public Sub WriteDates()
' Write month-end dates to files

Dim mysheet As Object
Dim xlApp As Object
Dim CurrDate As Date
Dim rsDates As Recordset
Dim TxtDate As String

' Set object variable equal to the OLE object.
Set xlApp = CreateObject("Excel.Application")

Set rsDates = CurrentDb.OpenRecordset("ME_Trade_Dates")
rsDates.MoveFirst

' Loop through all Months
Do While Not rsDates.EOF
    CurrDate = rsDates.fields("[ME_TD]")
    TxtDate = Format(CurrDate, "yyyymmdd")
    Set mysheet = xlApp.workbooks.Open("Y:\Susan and Rob\Equities\R1000\R1000_" & TxtDate & ".xls").Sheets(1)

' Put the value of the ToExcel text box into the cell on the
' spreadsheet and make the cell bold.
    mysheet.cells(2, 1).Value = CurrDate

' Set the Visible property of the sheet to True, save the
' sheet, and quit Microsoft Excel.
    mysheet.Application.windows("Y:\Susan and Rob\Equities\R1000\R1000_" & TxtDate & ".xls").Visible = True
    mysheet.Application.activeworkbook.Save
    mysheet.Application.activeworkbook.Close
    
    rsDates.MoveNext

Loop

xlApp.Quit

' Clear the object variable.
Set mysheet = Nothing

rsDates.Close

End Sub

Open in new window


What does this line do?

Thanks!
0
 

Author Comment

by:kobys
ID: 35218060
If I take that line out, the code seems to work but when I look at the Excel file, all of the cells referencing the cell I wrote say #NAME?.  The date looks perfectly fine and it is in the right cell.  What's going on?

Thanks.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:kobys
ID: 35218068
Forget my last comment.  It's an issue with Bloomberg.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35222510
To write a single record, you could create a filtered recordset, using code like the sample below to get just the single record you want, then export that saved query to Excel.
[sample code fragment using the procedure]

   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim rst As DAO.Recordset
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   
   strRecordSource = "tblInventoryItemsComponents"
   strQuery = "qryTemp"
   Set dbs = CurrentDb

   'Numeric filter
   lngID = Nz(Me![ID])
   If lngID <> 0 Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[ID] = " & lngID & ";"
   End If

   'String filter
   strInventoryCode = Nz(Me![InventoryCode])
   If strInventoryCode <> "" Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
   End If

   'Date range filter from custom database properties
   dteFromDate = CDate(GetProperty("FromDate", ""))
   dteToDate = CDate(GetProperty("ToDate", ""))
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   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
      'Use this line if you need a recordset
      Set rst = dbs.OpenRecordset(strQuery)
   End If

   'Use SQL string as the record source of a form
   strFormName = "fpriLoadSoldPackingSlip"
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acDesign
   Set frm = Forms(strFormName)
   frm.RecordSource = strSQL
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acNormal
   
   'Use SQL string as the record source of a report
   strReport = "rptLoadSold"
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewDesign, _
      windowmode:=acHidden
   Set rpt = Reports(strReport)
   rpt.RecordSource = strSQL
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewNormal, _
      windowmode:=acWindowNormal

   'The report has the filtered query as its record source

=========================

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35222513
The Numeric filter example would do for a standard AutoNumber key field.
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

739 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