Insert a blank row in Excel after Access export to Excel (1 Viewer)

BenMason

New member
Local time
Yesterday, 20:58
Joined
May 14, 2021
Messages
5
I have created an Excel Template. In Access I export data from a query to the Excel Template which works fine and it opens the Excel template so I can see the data that it exported. With the Excel template still open, how do I, with VBA, insert a blank row when column A date changes?
Example in column A:
4/18/2022
4/18/2022
4/19/2022

When it hits 4/19/2022, I want the code to know it's a different date from the previous date and will insert a blank row "above" 4/19/2022. This will continue for all dates in column A until the end of the record so that each date change will have a blank row inserted above it.

Not sure if this should be code from Access that after the export then insert the blank row to the active/open Workbook or have Access run an Excel macro after export to insert the blank row. Your help is appreciated.

Thanks,
Ben
 

June7

AWF VIP
Local time
Yesterday, 19:58
Joined
Mar 9, 2014
Messages
4,461
Your choice. Access VBA for Excel automation or Excel VBA to manipulate itself. Excel VBA could execute when workbook opens.

Either case, use macro recorder to get some basic code to start with. It will at least give you code for moving to next row and for inserting a row. As for the test for changed date, have to get creative with variables.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:58
Joined
Feb 19, 2002
Messages
36,908
There are two methods to do this from Access (unless you can figure out how to make an Excel macro that you can run from Access after you open the workbook.

1. Rather than using a bulk transfer method where all the rows are exported at once, use a VBA look that reads a row and compares the date to the previously saved date. If the previous date is Null (define the holdDate field as a variant to make this possible), save the date and write the record. If the previous date is the same, write the record (saving the date doesn't hurt. If the previous date is different but not null write the blank line, then save the date and write the record.
2. If you are not comfortable with VBA loops, you might try to do this with a couple of queries. Create a query that selects only the date and groups on it so you end up with one row per date. Then create a union query. The first select will select all the data you want to export, in the order you want to export the fields. Add a "dummy" field to the select clause with the value of 1. The second query will select the totals query. You will have to add dummy fields, just use "" or Null as place holders and for the final dummy field use a value of 9. Then create the final query. This one selects all columns and sorts by date and the final dummy value so all the populated records are followed by the empty record. Then uncheck the "dummy" column so it doesn't get exported.

PS, in order for #2 to work, date MUST be the primary sort field. The second sort field will be the "dummy" that contains the 1's and 9's, you can follow that with any other sort criteria.
 

Auntiejack56

Registered User.
Local time
Today, 13:58
Joined
Aug 7, 2017
Messages
168
Heya Ben,
This is a bit of fun, isn't it?
You obviously have your own code working, and I'm going to assume that the query that you use to export all the data is 'qryMyExportQuery', and that the date in the query is 'myDate'. So you could shoehorn something like this into it - I haven't tested it because you've already got the hard bits working, and you'd have to adapt this anyway:
Code:
Function goExportWithGaps()
Dim dbs As DAO.Database, rs As DAO.Recordset, strSQL As String
Dim dteStart As Date, dteEnd As Date, n As Long, dteDay As Date
Dim xlApp As Object 'Excel.Application
Dim xlWB As Object  'Excel.Workbook
Dim xlWS As Object  'Excel.Worksheet
Dim xcelRowCount As Long
Const qName = "qryMyExportQuery"

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Add
    Set xlWS = xlWB.Worksheets(1)

    Set dbs = CurrentDb()
    xcelRowCount = 2        '' Start on line 2
    dteStart = DMin("myDate", qName)
    dteEnd = DMax("myDate", qName)

    Do
        dteDay = DateAdd("d", dteStart, n)
        strSQL = "SELECT * FROM " & qName & " WHERE myDate = " & SQLDate(dteDay) & ";"
        Set rs = dbs.OpenRecordset(dbOpenSnapshot)
        
        ' Only send something to Excel if there are records found for this date
        If Not rs.EOF Then
            xlWS.cells(1, xcelRowCount).CopyFromRecordset rs
            ' Skip a row to add a blank line after the change of date
            xcelRowCount = xcelRowCount + rs.RecordCount + 1
        End If
        n = n + 1
    Loop Until dteDay = dteEnd

End Function

Obviously this would take longer to execute than other solutions, but it's neat and selfcontained, and you can (and should) pepper it with comments for the next guy or gal. You probably already have a function called SQLDate or similar, but if not it's easy to find a SQL Date formatter. After you do the CopyFromRecordset, the bookmark should be at eof, which will give you an accurate recordcount without needing to MoveLast.
Happy landings.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:58
Joined
Sep 21, 2011
Messages
10,853
I went with your original thoughts. :)

I did try For each range in range, but inserting the blank rows, mucked up the movement, so went with simple math.
The A3 start is assuming headers exist.

Code:
Sub InsertBlank()
Dim iLastRow As Integer

iLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Range("A3").Select

Do Until ActiveCell.Row > iLastRow
    If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then
        Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveCell.Offset(1, 0).Select
        iLastRow = iLastRow + 1
    'Debug.Print rngCell.Offset(-1, 0).Value & " - " & rngCell.Value
    End If
    ActiveCell.Offset(1, 0).Select
Loop


End Sub
This is just the basic logic?
 
Last edited:

Users who are viewing this thread

Top Bottom