Solved

Export with Workday

Posted on 2011-03-04
9
452 Views
Last Modified: 2012-05-11
Hi,

I have a table in Access 2003 called "GL Balances"

I would like to export this on a form to the below location but naming the file with "Balances 03/03/2011" but i would like the date to change each day. So its like excel =WORKDAY(Today(),-1)

Location

G:\Asset Services MI\UnMatched Merit\Balances

Does anyone have the code to achieve this?

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 35035439
It is impossible to save file with "/" in name.
You can use "_" or "-" or "."
0
 

Author Comment

by:Seamus2626
ID: 35035463
Yeah, theres no need for it to have /, 03032011 is fine

Thanks
Seamus
0
 
LVL 39

Expert Comment

by:als315
ID: 35035471
Dim path As String
path = "G:\Asset Services MI\UnMatched Merit\Balances\Balances "
path = path & Format(Now(), "mm_dd_yyyy.xls")
Then you can use this file name in export command.
0
 

Author Comment

by:Seamus2626
ID: 35035492
Im a code novice, so am womdering where i would put it in the export

I currently export with

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Balances", "G:\Asset Services MI\UnMatched Merit\Balances.xls", True, "GL Balances"

Thanks
Seamus
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Seamus2626
ID: 35035520
Also, that looks like today, is there workday-1 in Access code?

Thanks
Seamus
0
 
LVL 39

Expert Comment

by:als315
ID: 35035554
Dim path As String, D As Date
D = DateDiff("d", Now(), 1)
D = DateValue("05.03.2011")
While Weekday(D) = vbSunday Or Weekday(D) = vbSaturday
    D = DateAdd("d", -1, D)
Wend
path = "G:\Asset Services MI\UnMatched Merit\Balances\Balances "
path = path & Format(D, "mm_dd_yyyy.xls")
0
 
LVL 39

Expert Comment

by:als315
ID: 35035557
Remove D = DateValue("05.03.2011") - it was test
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 35035563
Dim path As String, D As Date
D =  DateAdd("d", -1, Now())
While Weekday(D) = vbSunday Or Weekday(D) = vbSaturday
    D = DateAdd("d", -1, D)
Wend
path = "G:\Asset Services MI\UnMatched Merit\Balances\Balances "
path = path & Format(D, "mm_dd_yyyy.xls")
0
 

Author Closing Comment

by:Seamus2626
ID: 35035697
Thanks!

Seamus
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now