Exporting to excel on a network drive

[GraemeG]

Hello.

I am currently using a macro to output tables to an excel file on a network drive.
This all works great but I was wondering whether I could use VBA to do this but incorporate a date into the file.
I.e. currently the file goes to the drive and sits there but next time it is run it tries to overwrite. So i was wondering if incorporating a date into the extract (File name which includes date of extraction) could be done?

Any help much appreciated!

 

[DCrake]

Convert the macro to VBA and recode it acordingly to append a date to the file name.

 

[GraemeG]

Convert the macro to VBA and recode it acordingly to append a date to the file name.

    DoCmd.OutputTo acOutputTable, Internal_Survey_Data, Excel, "[URL="file://\\ipaddressgoeshere\sis\CORE\HOLD\SIS_INTERNAL_SURVEY_DATA.xlsx"]\\ipaddressgoeshere\sis\CORE\HOLD\SIS_INTERNAL_SURVEY_DATA.xlsx[/URL]", no

This does not seem to give me any joy debuging "2487" saying object type argument is blank or invalid. but cant see anywhere to insert the date on the file format?

 

[DCrake]

Dim sFile as string

sFile = "SIS_INTERNAL_SURVEY_DATA_" & Format(Date(),"YYYYMMDD") & ".xlsx"
 
    DoCmd.OutputTo acOutputTable, Internal_Survey_Data, Excel, "\\ipaddressgoeshere\sis\CORE\HOLD\" & sFile , No

 

[GraemeG]

Private Sub cmdSync_Click()
If MsgBox("Warning! You must be connected to the network to synchronise" & vbNewLine & vbNewLine & "If you wish to proceed please select Ok" & vbNewLine & "If you do not wish to proceed please select Cancel", vbExclamation + vbOKCancel, "Synchronise Data") = vbOK Then
Dim sFile As String
sFile = "INTERNAL_SURVEY_DATA_" & Format(Date, "YYYYMMDD") & ".xlsx"
    DoCmd.OutputTo acOutputTable, Internal_Survey_Data, Excel, "[URL="file://\\ipaddresshere\sis\CORE\HOLD\"]\\ipaddresshere\sis\CORE\HOLD\[/URL]" & sFile, No
End If
End Sub

Still getting a "2487" - The object type argument for the action or method is blank or invalid!

 

[DCrake]

Have you tried putting the table in quotes?

 

[GraemeG]

Have you tried putting the table in quotes?

That takes me one step further and pops up with an output selection box to slect the type of Excel File type. (Can this be set to Excel Workbook as standard?) and then also once I make a selection it states that the object does not exist.

 

[DCrake]

It could be that it does not like using IP address in file name, can you not use the actual network path?

 

[GraemeG]

It could be that it does not like using IP address in file name, can you not use the actual network path?

Sorry that gives me the same issue.
just a note - the file does not exist as of yet. That wont be causing issues will it. Because the idea is for it to create a new file everytime with a new date.

 

[GraemeG]

And to incorporate the date/time:

DoCmd.OutputTo acOutputTable, [COLOR=red][B]"[/B][/COLOR]Internal_Survey_Data[B][COLOR=red]"[/COLOR][/B], [B][COLOR=red]acFormatXLSX[/COLOR][/B], "[URL="file://\\ipaddressgoeshere\sis\CORE\HOLD\SIS_INTERNAL_SURVEY_DATA" & Format(Date, "yyyymmdd_hhnn") & ".xlsx"]\\ipaddressgoeshere\sis\CORE\HOLD\SIS_INTERNAL_SURVEY_DATA" & Format(Date, "yyyymmdd_hhnn") & ".xlsx[/URL]"

Thanks. Is there also a way to include the current logged in user in the filename for extract? The current user logged in is by fields fldUsername and fldLoggedIn in another table tbl-users?

 

[GraemeG]

Do you use a login user name of something other than the user's network login? If it was the network login, it would be simpler. It could be:

Dim strUser As String

strUser = VBA.Environ("username")

DoCmd.OutputTo acOutputTable, "Internal_Survey_Data", acFormatXLSX, "\\ipaddressgoeshere\sis\CORE\HOLD\SIS_INTERNAL_SURVEY_DATA" & strUser & Format(Date, "yyyymmdd_hhnn") & ".xlsx"

Erm the tablets this software is going onto doesnt have login's but could I could set them up with users if necessary if it would make it easier?

 

[GraemeG]

Private Sub cmdSync_Click()
Dim strUser As String
strUser = VBA.Environ("username")
If MsgBox("Warning! You must be connected to the network to synchronise" & vbNewLine & vbNewLine & "If you wish to proceed please select Ok" & vbNewLine & "If you do not wish to proceed please select Cancel", vbExclamation + vbOKCancel, "Synchronise Data") = vbOK Then
    DoCmd.OutputTo acOutputTable, "Internal_Survey_Data", acFormatXLSX, "[URL="file://\\ipaddress\sis\CORE\HOLD\InternalSurveyData"]\\ipaddress\sis\CORE\HOLD\InternalSurveyData[/URL]_" & strUser & Format(Date, "yyyymmdd") & ".xlsx"
    DoCmd.OutputTo acOutputTable, "External_Survey_Data", acFormatXLSX, "[URL="file://\\192.156.174.254\sis\CORE\HOLD\ExternalSurveyData"]\\[COLOR=#0000ff]ipaddress[/COLOR]\sis\CORE\HOLD\ExternalSurveyData[/URL]_" & strUser & Format(Date, "yyyymmdd") & ".xlsx"
    DoCmd.OutputTo acOutputTable, "Communal_Internal_Survey_Data", acFormatXLSX, "[URL="file://\\192.156.174.254\sis\CORE\HOLD\CommunalInternalSurveyData"]\\[COLOR=#0000ff]ipaddress[/COLOR]\sis\CORE\HOLD\CommunalInternalSurveyData[/URL]_" & strUser & Format(Date, "yyyymmdd") & ".xlsx"
    DoCmd.OutputTo acOutputTable, "Communal_External_Survey_Data", acFormatXLSX, "[URL="file://\\192.156.174.254\sis\CORE\HOLD\CommunalExternalSurveyData"]\\[COLOR=#0000ff]ipaddress[/COLOR]\sis\CORE\HOLD\CommunalExternalSurveyData[/URL]_" & strUser & Format(Date, "yyyymmdd") & ".xlsx"
    DoCmd.OutputTo acOutputTable, "HHSRS_Survey_Data", acFormatXLSX, "[URL="file://\\192.156.174.254\sis\CORE\HOLD\HHSRSSurveyData"]\\[COLOR=#0000ff]ipaddress[/COLOR]\sis\CORE\HOLD\HHSRSSurveyData[/URL]_" & strUser & Format(Date, "yyyymmdd") & ".xlsx"
End If
End Sub

I am back in the office and I am getting another error with this now:
Run-time error - 3021
No Current Record

 

[GraemeG]

Resolved!!
All working - changed the output to a query instead of table and works a treat!