none
Data missing when exporting, from a query, to excel RRS feed

  • Question

  • I'm not really sure where I'm going wrong.  Hopefully someone much wiser, can shed some light.

    Some of our data has to be exported to an excel spreadsheet, and e-mailed off, on a daily basis.  The data though, must be in a provided template.

    The fields, being exported, are [Student #] (Number), [Student Name] (Short text) , [Fine Date] (Date & Time), [Fine Description] (Short Text), [Price] (Currency), and [Notes] (Short Text).  The table has a [Date Modified] (Date & Time) field as well.

    *Note that the save as location is currently saving to the desktop, but I will be changing this when pushing this live

    Private Sub Form_Open(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim xlObj As Object, Sheet As Object
    Dim xlFile As String
    xlFile = "\\servername\folder share\sub folder\Student Fine Template.xlsx"
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open xlFile
    Set rs = Me.Recordset
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")
    Sheet.Range("A7").CopyFromRecordset rs

    xlObj.ActiveWorkbook.SaveAs "C:\Users\XXXX\Desktop\Fines " & Format(Date, "mm-dd-yy") & ".xlsx" 
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
    End Sub

    When the form "Fines", is opened, this runs, and the file is created.  Upon opening the Excel document, everything is exactly where it should be (data from all fields).

    Now, since this will need to be ran every day, I couldn't have it set up to retrieve every record.  So, I added a [Date Modified] field, to reflect when a record was updated.  So far, so good.

    I have it set up so that it only shows results where the record contains a [Date Modified] of today (as fines are paid, the records would be update, thus the reason for the date modified field).

    To test this out, I created a form based on a query called "Fines Today".  I enter the same code, as above, open the form, and the file is created.  

    The problem, though, is that when I open the file, anything in the [Description] field, is not copied over.  It instead copies the data from the [Price] field where the info from [Description] should be, and so on and so forth.

    If I open the form, and manually export (External Data --> Excel), all of the information is there.

    Does anyone have an idea as to why the data from that field would not copy over, when a query is used?

    Monday, December 11, 2017 11:37 PM

Answers

  • After a cup of coffee, and a fresh set of eyes, I realize, now, where my mistake was.  It turns out I missed the obvious, and that there was a hidden column in the template.  So, data is being written there.  I'll have to make a few tweaks, so that it is skipped over.

    Thank you for the responses.


    • Edited by bensim123 Tuesday, December 12, 2017 4:26 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, December 13, 2017 1:29 AM
    • Marked as answer by bensim123 Wednesday, December 13, 2017 2:45 PM
    Tuesday, December 12, 2017 4:26 PM

All replies

  • Hello bensim123,

    What's the query string? Is there a Description field in the form?

    If you try to iterate through the recordset's field, would you see the Description field?

    Here is the  example.

    For Each fld In rs.Fields
    Debug.Print fld.Name
    Next fld

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 12, 2017 1:36 AM
  • I agree with Terry, I don't see a [Description] field either. You may want to indicate how the DAO Recordset was created and specify the SQL statement you used.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, December 12, 2017 2:10 PM
  • Sorry, that was a typo.  When I wrote [Description], I meant [Fine Description]

    The SQL statement reads:

    SELECT [Fines].[Student #], [Fines].[Student Name], [Fines].[Fine Date], [Fines].[Fine Description], [Fines].Price, [Fines].Notes, [Fines].[Date Modified]
    FROM [Fines]
    WHERE ((([Fines].[Date Modified])=Date()));


    • Edited by bensim123 Tuesday, December 12, 2017 3:32 PM
    Tuesday, December 12, 2017 3:27 PM
  • When I iterate through the recordset, all of the fields show in the Immediate window, including [Fine Description] (sorry, the [Description] field was a typo in my original post, it was meant to read [Fine Description]
    Tuesday, December 12, 2017 4:11 PM
  • After a cup of coffee, and a fresh set of eyes, I realize, now, where my mistake was.  It turns out I missed the obvious, and that there was a hidden column in the template.  So, data is being written there.  I'll have to make a few tweaks, so that it is skipped over.

    Thank you for the responses.


    • Edited by bensim123 Tuesday, December 12, 2017 4:26 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, December 13, 2017 1:29 AM
    • Marked as answer by bensim123 Wednesday, December 13, 2017 2:45 PM
    Tuesday, December 12, 2017 4:26 PM
  • Hello bensim123,

    I'm glad to hear that you could find the reason. I would suggest you mark your reply to close this thread. If you have any other issue, please feel free to post threads to let us know. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 13, 2017 1:41 AM