Solved

Export with Workday

Posted on 2011-03-04
9
436 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
Comment Utility
It is impossible to save file with "/" in name.
You can use "_" or "-" or "."
0
 

Author Comment

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

Thanks
Seamus
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Thanks
Seamus
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
Remove D = DateValue("05.03.2011") - it was test
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
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
Comment Utility
Thanks!

Seamus
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 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

18 Experts available now in Live!

Get 1:1 Help Now