Why Excel maro won't work with transfer spreadsheet.

I have an .xlsm file with the following code I found through Experts-Exchange.  I originally used =TODAY but that changed every time I opened the file.  I need the date to be entered automatically into the cell and remain that date.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(3, 18)
                    .Value = Now
                    .NumberFormat = "mm/dd/yyyy"
                End With
                rCell.Offset(3, 18).Clear
            End If
    End If

    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
    MsgBox Err.Description
    Resume ExitHandler
End Sub

I have a button in Access that when clicked transfers the data to a spreadsheet and saves it as a new Excel file.  I don’t understand why the following code will work if I type something into a cell in column A but won’t if I transfer data to column A using transfer spreadsheet.  When I transfer the text the information in the cells changes so why won’t it fill in the date?  I typed the word “Date” in cell A1, in the .xlsm file, thinking it had to change from one text to another but that didn’t do anything either even though the word “Date” changed to “First Name”.

I have also tried a circular reference but that won’t work either.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Wouldn't it be easier to add a column for the current date in the query that you are exporting?
Annmv888Author Commented:
I only need it once not for every single item.  There is a section on the spreadsheet for a count, amount and date.  Everything works fine but the date because I can no longer use =TODAY since I now have to retain them.
TransferSpreadsheet only transfers data.  It doesn't run any macros.  If you want to run the macro, use OLE to open the spreadsheet after the TransferSpreadsheet and run the macro then.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Annmv888Author Commented:
I'm not sure what ole is but will research.

However, I understand that the macro is supposed to run when data in column A changes.  So if the cells are blank except for a1 and the word date is in the cell when the data is transferred to the spreadsheet and a1 and all blank cells change why isn't that considered a change that will run the macro?  If I type a word in the blank cell it will works.  If I copy and paste it also works.
Fabrice LambertConsultingCommented:
I'm not sure what ole is but will research.
In other words, it is automating Excel from Access, instead of performing a transfertSpreadSheet.
TransferSpreadsheet DOES NOT cause any macros to run, period!  I don't have any example but searching should come up with several options.  Search for "run macro in excel from access"
Rob HensonFinance AnalystCommented:
How about running the script from Excel so that it pulls the data rather than pushing from Access?

You can set a data connection to the database or a query within the database if you don't need all the data. That connection can then be refreshed as part of the script and update the date.
Annmv888Author Commented:
I just wanted to thank everyone for the help.  At this time I am no longer going to pursue this.  This is a database in Access and I don't want to redo any of the work that has already been completed.  This used to work fine when the spreadsheet was overlaid each time it was created.  I'm not a programmer (just beginning) nor do I work in Excel.  I work in Access.  I can't believe how frustrating Excel is.  Every time one issue is resolved there is another one.  At this point the user will have to open the file and change something in column A and the date code will work.  It's not what I wanted but have spent way too much time on this already.  Thanks again for all the help and ideas I received it is very much appreciated.
Annmv888Author Commented:
I guess I didn't give up and was able to figure something out.  I recorded a macro to copy and paste cell a1 so the cell changes which triggers the code I started with that didn't work.  Because it's part of the .xlsm file it is part of every file that is created during the transfer spreadsheet event.  I then added more code to the Access button so once the Excel file is created it gets opened, the open code runs the copy and paste macro and the date is hard coded into the cell where it's needed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Good going.  There is always another way.  Thanks for sharing.
Annmv888Author Commented:
I was able to resolve the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.