Export to excel with Date format

[Yale_work]

I have a code in VBA to export data in excel. this works fine and add a date in exported file name. But the Access macro does not like the following code while VBA works fine..

"PATH\filename" & "_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".xlsx"

When I create a macro in excel, it asks me to create "ExportWithFormatting"...in the output file I am writing the above mentioned code. It stops the macro while if I run it through VBA (step by step) it works fine.

How can I bring my VBA code and Macro in sync. I am converting MACRO to Visual BASICS and then modifying the VBA code. I think if I save my VBA code, it should reflect in macro

 

[Gasman]

I think if I save my VBA code, it should reflect in macro

Where did you get that idea? It is called Convert for a reason.

Personally I would scrap the macro and stick with VBA, much more flexible, much much much easier to test and debug.

Hang on, just reading this again to see what Access macro you used?
So to clarify, are we talking Access Macro and VBA or Excel macro and VBA ?

FWIW in the immediate window

? Format(Date,"yyyymmdd") & "-" & Format(Time,"hhmmss") & ".xlsx"
20220717-111313.xlsx

If Access macro, what macro?

 

[Eugene-LS]

"PATH\filename" & "_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".xlsx"

I offer you a shorter way of generating the path string:

...  = "PATH\filename" & Format(Now, "\_mmddyyyy\_hhmmss") & ".xlsx"

 

[Gasman]

I cannot get it to work either

 

[Gasman]

I offer you a shorter way of generating the path string:

...  = "PATH\filename" & Format(Now, "\_mmddyyyy\_hhmmss") & ".xlsx"

That still does not work?
Macro does not seem to like constructing a filename as you can with VBA?
I even tried a TempVar

 

[Gasman]

One workaround if you absolutely insist on using an Access macro, is to make that VBA code a function and use the macro to run that?
However, they will never be in sync, if that is your actual question. Once converted, they are two different objects.

 

[CJ_London]

is "PATH\filename" viable in this context or has the OP hidden the real path and filename for confidentiality reasons

I assume the code is in access exporting date to excel but really need to see the vba code and macro being used

 

[Gasman]

I could not get it to work with "c:\temp\test" & and the format functions.
Also tried starting macro field with =

 

[Yale_work]

Where did you get that idea? It is called Convert for a reason.

Personally I would scrap the macro and stick with VBA, much more flexible, much much much easier to test and debug.

Hang on, just reading this again to see what Access macro you used?
So to clarify, are we talking Access Macro and VBA or Excel macro and VBA ?

FWIW in the immediate window

? Format(Date,"yyyymmdd") & "-" & Format(Time,"hhmmss") & ".xlsx"
20220717-111313.xlsx

If Access macro, what macro?

Thanks for your response. It is Access macro and VBA. In excel it is easy as you can assign a subroutine to a button. In Access you have to assign a macro to a button and your macro and VBA code are different.

Code "PATH\filename" & "_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".xlsx" does not work in macro.
So I am getting VBA code from macro and adding date/time string but that does not reflect in Macro.

My objective is to get DATE/TIME added in the exported excel file name.

 

[Yale_work]

One workaround if you absolutely insist on using an Access macro, is to make that VBA code a function and use the macro to run that?
However, they will never be in sync, if that is your actual question. Once converted, they are two different objects.

Can you please share some steps to do that? I am ok with this approach. You are suggesting to create a subroutine and call that subroutine in macro...correct? I think that works ok in excel but not in access....request you to suggest some steps..

 

[Yale_work]

Can you please share some steps to do that? I am ok with this approach. You are suggesting to create a subroutine and call that subroutine in macro...correct? I think that works ok in excel but not in access....request you to suggest some s

 

[Yale_work]

Getting some help from google ...

 

[CJ_London]

Can you please share some steps to do that?

really not sure what your problem actually is, but if it is to use vba rather than a macro, in your form design, select a control and view properties - if properties not visible then select either from the properties button on the design ribbon or by right clicking on the control and selecting properties..

Go to the Event tab and for the event you want to code, click on the 3 dot carat

this will give you 3 choices

select code builder and that will take you to the VBA editor

If you don't want to use macros at all for for form and control events, you can go to file>options>object designers and tick the option 'always use event procedures'

then click OK.

Clicking the 3 dot carat will then take you straight to the vba window

 

[Yale_work]

thanks

 

[Yale_work]

thanks

Any idea how can I format the column in exported excel sheet? I have a calculated field X/Y and I see the output as 50.14 in Access Query. When I export this into Excel using VBA, this becomes 50.13567890....I want to have 50.14 in exported excel

 

[Gasman]

Thanks for your response. It is Access macro and VBA. In excel it is easy as you can assign a subroutine to a button. In Access you have to assign a macro to a button and your macro and VBA code are different.

Code "PATH\filename" & "_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".xlsx" does not work in macro.
So I am getting VBA code from macro and adding date/time string but that does not reflect in Macro.

My objective is to get DATE/TIME added in the exported excel file name.

No!, you have a choice of Macro or Event Procedure. I always choose the latter
In fact I have the latter as default. I can only show you the picture below, as I switched back on that option for someone else having problems with macroes. They are nightmare to debug.
Seriously, just use VBA

Edit: Only just seen the posts after where the site took me and CJ_London has shown you where the default setting is as well.

 

[Gasman]

Can you please share some steps to do that? I am ok with this approach. You are suggesting to create a subroutine and call that subroutine in macro...correct? I think that works ok in excel but not in access....request you to suggest some steps..

OK. Here is a pic from a switchboard of a DB I created.
8 is the number for a macro, and you can see the name of the function. (Has to be a function, macroes will not tun a Sub)

Then here is my code that gets run for backing up FE

Function CreateBackupFE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("FE")
End Function

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
   
   
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("tblUser")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
   
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
   
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
   
   
End Sub

And the switchboard is actually macro driven, but that is it.
Here is the part for a macro

 

[CJ_London]

and I see the output as 50.14 in Access Query. When I export this into Excel using VBA, this becomes 50.13567890....I want to have 50.14 in exported excel

you are using formatting to only display 2dp -formatting is ignored when doing calculations and importing/exporting which will use the underlying data.

Use the round function to limit to 2dp

round(X/Y,2)

Note using calculated fields is not recommended - do your calculation in a query as and when required