How To Append Date To Filename??

Jun 28, 2005

I'm making a macro to output a report to an Excel spreadsheet using OutputTo and would like to add the date to the filename since this is done each day and the files need to be different. So I'd have something like "Output" as the base filename and "Output20050628" as the final filename. Would someone please help me out on this? Thanks in advance.

View Replies


ADVERTISEMENT

Extract Date From Filename

Apr 12, 2008

I think I am on the right track, but I am stuck with importing the date from my excel filename. Currently the following code imports all Excel files from the directory into a table in my database. The excel files are saved like "ABC_BNG_GTR_04012008.XLS" The numbers represent the date. I need for the date to be extracted and place in table into the "callDate" field. When I run my code, I continue to get a runtime error 13 - type mismatch. The code stops at the Mid() statement. What am I doing incorrectly? Thanks in advance.

Option Compare Database

Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim TheDate As Date


DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData"

'Loop through the folder & build file list
strFile = Dir(path & "*.xls")

While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sheet2", filename, False

'Inserts date in date column based on file name

TheDate = Mid(strFile, 12, 8)

CurrentDb.Execute "UPDATE sheet2 SET callDate =" & "'" & TheDate & "' where callDate is null"


Next intFile

DoCmd.SetWarnings True

End Sub

View 14 Replies View Related

InsertFile Filename

May 13, 2005

dears members,

I m trying to add document2.doc to document1.doc by clicking a button on a form.

I use the next VBA code.

Selection.InsertFile filename:="document2.doc", Range:="", ConfirmConversions :=False, Link:=False, Attachment:=False

I get the following message error:

Err.number = 5174
Err.Description = "The file cannot be found"
Try one of the following things:
* Check if you write the name good
* try an other file name (document2.doc)

The file document2.doc does really exist and is saved in the same directory as document1.doc

the problem is sometime I get the error message some times not in this last case it works very good.

can any body help me ?

Thanks in advance.

View 1 Replies View Related

Append X No Of Records From Date

Jul 20, 2005

Hi all,

Am a bit stuck with this one.

I have a table "tblReviews" with the following fields:-

Learn_ID
Review_PlannedDate
Review_ActualDate
Reviewer

I have an unbound form with the following txt boxes:-

txtLearn_ID
txtReviewStartDate (Format dd/mm/yyyy)
txtIntervalWks
txtAmountOfReviews (Number)
txtReviewer

What I would like to happen is after I have completed the above fields, appends the rows to my table.

So if

txtLearn_ID = Ajwebb0982
txtReviewStartDate = 01/09/2005
txtIntervalWks = 1
txtAmountOfReviews = 4
txtReviewer = Ann

I would like to Add 4 rows to my table which should look something like the following:-

Learn_ID, Review_PlannedDate, Reviewer
Ajwebb0982, 08/09/2005, Ann
Ajwebb0982, 15/09/2005, Ann
Ajwebb0982, 22/09/2005, Ann
Ajwebb0982, 29/09/2005, Ann

I'm sure it is possible, just stumped on how to go about it. :confused:


Thanks in advance for any help.


Taff.

View 2 Replies View Related

Append A Date Range

Nov 27, 2006

Hi Guys

Can someone please help me with this? I have an Events form which has some date fields setupdate and enddate. There is a linked events calendar subform attached. I have attached the database for you to see.

If you open the events form and go to the events date tab, I want to be able to type in the dates in the setup and end date fields, and then run a query which populates the events calendar subform. I created a date table (dates) with a list of dates and tried to create an append query (Query3) with those fields as criteria which didn't work as it wasn't recognising the parameters (but i'm guessing that is because it wasn't attached to the Events Form). However after entering the dates, I was able to append them to the events calendar only after including the EventsID field to the dates table and the query. This however means that the records aren't linked to the Events table as the EventsID field is blank (the EventsID is what links the E.Calendar subform to the Events Form). Therefore though the records were reflected in the events calendar form, it wasn't showing up on the events form.

I dont know if this makes much sense but what I basically want to do is a series of functions. Create a query which extracts a set of dates based on criteria entered into the main form, and appends them to a datasheet subform which is linked to the main Events form via the EventsID, then copies the Event ID for all the recordsets that have been added.

Is this possible? Thanks for your help in advance.

View 2 Replies View Related

Append Events By Date

Jun 23, 2007

I have a series of events that have a start date and an end date. How do I write an append query to append an event to a table as an individual event for every day in the given range?

View 2 Replies View Related

Newbie - Help Getting Right Date To Append

Sep 25, 2004

This is my first stab at this and I'm obviously overlooking something. I'm trying to do a recurrent date append to an Access calendar db. I'm trying to take the field varStartDate and add 7 to it for the # of weeks recurring. Works great. I type the var out and it is appending correctly BUT when I look at the db, it is putting the ORIGINAL START_DATE and not the RecurDate var. What am I doing wrong?

TIA!
Lisa

Dim strSql
startDate = CDate(Request.Form("varStartDate"))
IF Request.Form("numWeeksRecur") > 0 THEN
Weeks = Request.Form("numWeeksRecur")-1 ' *** Subtract week from total week for first Command.Execute
For recurDate = startDate To startDate+7*Weeks Step 7
fldStartDate = recurDate
fldStartTime=Request.Form("varStart_Time")
fldEndDate=Request.Form("varEnd_Date")
fldEndTime = Request.Form("varEnd_Time")
fldeventTitle=Request.Form("txtEvent")
fldevt_grp = Request.Form("txtgroup")
fldlocation = Request.Form("txtLocation")
strSql = "INSERT INTO events (start_date, start_time, end_date, end_time,eventTitle, evt_grp) VALUES fldStartDate, "
'FOR DEBUG ONLY
Response.Write(fldStartDate) & "<hr>"
Response.Write(recurDate) & "<hr>"
'Response.End
MM_editCmd.Execute strSQL
Next
END IF

View 2 Replies View Related

Headache With Changing The Filename... ...

Oct 19, 2006

Hi all,

I got a headache to changing my form's filename... i am totally a newbie in Access while in the few months ago... so i just create all those form or table name's for the way i like. But now i had already know how important of management of the filename because when u r using the module to write the code u will be headache to look for the filename that u want in the code. So that is important to add 'tbl', 'frm', 'Qry' infront of the filename.

So do anyone have a good way to edit all the file name with all match to the other in the module with a quick way rather then one by one editing (really tired for one by one) Thanks!!!

View 4 Replies View Related

How To Display Current .mdb Filename?

Sep 8, 2004

I have a replicated database. I'd like to display the name of the version of the database that is being used on the data entry form so it's clear which version is being used.

What's the code for the current filename? (I'm talking about the entire "filename.mdb" file).

View 4 Replies View Related

Adding The Value Of A Listbox To A Filename

Aug 10, 2006

Good morning,
I have a listbox that contains dates (0105, 0205, 0305, etc.) and depending upon which date is selected in the list box, I want to import the corresponding month's text file. Here's the code:

Private Sub List64_DblClick(Cancel As Integer)
Dim frm As Form, CTL As Control
Dim VARITM As Variant
Dim X As Integer
Dim period, PERIOD1 As String
period = Me!List64.Column(4)
PERIOD1 = Me.List64.Column(0)

DoCmd.TransferText acImportFixed, "df import specification", "LOCAL DF-SL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconDF_" & PERIOD1

DoCmd.TransferText acImportFixed, "Rf import specification", "LOCAL RF-SL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconRF_" & PERIOD1

DoCmd.TransferText acImportFixed, "MDdf_import_spec", "LOCAL DF-GL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconMDDF_" & PERIOD1

DoCmd.TransferText acImportFixed, "MDRf_import_spec", "LOCAL RF-GL", "S:FinanceAccounting OperationsNational AccountsAccount Recs116406-Account ReconMDRF_" & PERIOD1

So I have the files saved on the above mentioned S: drive, i.e., MDRF_0105 and when the user selects 0105 in the listbox, I want the appropriate file to be imported.

This code is set on the DblClick event.

Please help!

Thank you!

View 3 Replies View Related

How To Get Filename To Show Up Next To Attachment Box

Mar 13, 2012

Is there a code to get a filename to show up next to an attachment box?

View 2 Replies View Related

Append Query + Date Incriment

Jul 20, 2005

I want a query that will append a date from one table to another, however I want it to incriment by one day. Is this possible? For example:

tblTable1
07/20/2005
07/25/2005
07/28/2005

ActionQuery - tblTable1 appends to tblTable2 and incriments one day:

tblTable2
07/21/2005
07/26/2005
07/29/2005

Is there a way?

View 2 Replies View Related

Using An Append Query With Incrementing Date?

Aug 30, 2007

Hi,

Here's my problem. I have a table that is for maintenance jobs that recur on a fixed schedule, i.e. cleaning gutters yearly. I need to run an append query that will take all the information and cause it to recur a given number of times. I've gotten that to work with just a standard append query pulling data from a form.

The part I'm having trouble with is this. I need to get that append query to take the start date for the job and increment it properly based on my selection of daily/monthly/yearly recurrence for each individual occurance of the job.

Hope someone can help,

Thanks,
Brian

View 6 Replies View Related

Change Filename Of Exported Textfile

Jun 6, 2006

Hi All,

I have this line of code:

DoCmd.TransferText acExportDelim, "Dhcp Specification", "tblDHCP", "U:" & [Forms]![frmExportDHCP]![cmbServer] & ".txt", False, "", 850

Where the section highlighted is a combo box on a form. I was trying to use the value in this combo box to describe the filename of the text file I was exporting to. However I get the error:

The Microsoft Jet Databse Engine could not fond the object 'X#txt'. Make sure the object exists and the you spell it name and path name coreectly.

Where X is the string stored in the combo box.

Any ideas where I have gone wrong/what could be improved?

Cheers,

Matt

View 1 Replies View Related

Saving An Email With A Unique Filename

Nov 3, 2004

I am trying to create a unique filename when sending a report via email in snapshot format.

As already stated in another thread, the filename comes from the report's "caption" property.

Can a unique filename be given in a case like this? I need the filename to be a series of concatenations of
field names.

This report will be sent one page at a time. And sent by seperate people as many as 100 times in a single month. And as much as 1000 times in a single year. Each page (report) will be stored on a sever. So saying that, the need for a unique filename for each time the report is emailed is very important.

Thanks for any assistance on this.

Tasha

View 14 Replies View Related

Modules & VBA :: GET Filename Directly From Path

Sep 18, 2014

For instance, I got some dynamic length path with filename as follows:

F:OUTDOOR9-8W1250832 ABCworking 1.3-0.6 BOARD 720.tif

Is there any easy way to get only the "working 1.3-0.6 BOARD 720.tif", which is the desired filename.

View 2 Replies View Related

General :: CSV Files - Importing Filename That Changes

Jul 2, 2015

I need to import a number of .csv files every day and overwrite the old tables with the data from the new .csv files. However, each day, the filenames will change.

For example,

Day 1: Import fileA 1.csv, fileB 1.csv, fileC 1.csv
Day 2: Import fileA 2.csv, fileB 2.csv, fileC 2.csv
Day 3: Import fileA 5.csv, fileB 5.csv, fileB 6.csv

The files will be named fileA [#].csv, fileB [#].csv, fileC [#].csv, and so on, with a different number each day (with no pattern to the numbers).

Is there a way to write an import specification or VBA script that will import these? Perhaps something that allows a regular expression in the file path so I can import "C:fileA*", "C:fileB*", etc.?

View 5 Replies View Related

Queries :: Append Time Onto Short Date

May 14, 2013

I have a totals query that displays the sum of what products we ship each day. A process in the system automatically assigns the date/time to a ShippingDate field when an item is marked as despatched.

The totals query allows users to view what products are shipped between a period specified by the user.

The problem I am getting is that when the user inputs dates into fields [txtStart] and [txtEnd], the query fires up but will not display any records as a start time 00:00:00 and end time 23:59:59 was not input.

I don't want users to have to do this but cannot think of a way around it. My criteria code in the query for the shipping dates is

Code:
Between [Forms]![Switchboard]![txtStart] And [Forms]![Switchboard]![txtEnd]

Can I append the start / end time onto the code above somehow or is there another method I can utilise that is probably so glaringly obvious I have missed.

Usually a query allows me manipulate a field property sheet but in this case there is nothing obvious to select like format.

View 2 Replies View Related

Strip A Filename String Of Any Disallowed Characters

May 8, 2006

I am writing some search results to an excelsheet for reports using the time and date functions to build a filename. I downloaded a StripSpecialCharacters() module, but it doesn't take them all out (only takes ascii above 127).

Does anybody know of another function to do it. searched the archive to no avail.

Thanks in advance

View 5 Replies View Related

Queries :: Formula To Capture Filename In Query?

Jan 25, 2014

Need a formula which can capture filename for me. For example, I have a db saved at "C:DlocationofficeChina.accdb". Is there a way to capture "China" in a query?

View 2 Replies View Related

Modules & VBA :: Set Filename / Location In Save As Dialog

May 7, 2015

The code below sets my printer to "Cute PDF writer" and then opens a report in print view, which creates a PDF version of my report. So far so good. But just before the last step, it brings up the Save As dialog box, asking for a filename and location.

Three points are important:I always want to call the PDF document "Specialty Report".I always want to save it to C:Documents and Settings80435 Desktop. I always want to overwrite the previous version.

Is there some code I can insert that would achieve this automatically with no further user interaction i.e. user clicks a button and the PDF file is created/overwritten?

Code:

'Produce the report
Dim defPrinter As String, NewPrinter As Printer
defPrinter = Application.Printer.DeviceName 'Get the default printer name
Set NewPrinter = Application.Printers("CutePDF Writer") 'Create a new printer object
Set Application.Printer = NewPrinter 'Set the default printer to the new printer
DoCmd.OpenReport "Specialty Report", acViewPrint 'Open the report in print view (which will PDF it)
Set NewPrinter = Application.Printers(defPrinter) 'Reset the printer back to the default printer

View 3 Replies View Related

Modules & VBA :: Inserting Filename And Path Into Table

Jan 8, 2014

I have a table named tbl_imagepaths with fields:

ImageID
File
Folder

The command button has the code below, it works great and pops up with a window with the file name and path. I now just need the code to insert the file name and path into the table....

Code:
Private Sub Toggle7_Click()
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)

[Code] .....

View 7 Replies View Related

Queries :: Take A Date From Textbox On A Form Into Append Query

Nov 26, 2013

how do you take a date from a textbox on a form into an Append query and increase the date entered into the textbox by 1 day.This is what I have so far but not working??

MealDate: [Forms]![FrmSwitchBoard]![txtweekend]=DateAdd("d",1,Date())

View 4 Replies View Related

Modules & VBA :: Rename Table - Append Current Date On The End

Jul 2, 2014

I am using access 2010. I need to rename a table when I import the a new table. But I can't get the docmd rename code to work. I also want to append the current date on the end i.e.

docmd.rename, "tblOldTable(Currentdate)", actable, "tblOldTable"

doesn't work.

View 10 Replies View Related

General :: Opening DB With A Space In Filename Via Command Line

Jan 21, 2013

I am working on supporting spaces in the DB filename. I got the rest of my program working... other than being able to invoke Access with the specified filename for it to open.

Code:
C:Documents and Settingsc_mlueckMy DocumentsFandango>"C:Program FilesMicrosoft OfficeOffice12MSACCESS.EXE" "Schema Ideas.accdb"

C:Documents and Settingsc_mlueckMy DocumentsFandango>dir "Schema Ideas.accdb"
Volume in drive C has no label.
Volume Serial Number is C8E4-5856

Directory of C:Documents and Settingsc_mlueckMy DocumentsFandango

01/18/2013 04:45 PM 3,203,072 Schema Ideas.accdb
1 File(s) 3,203,072 bytes
0 Dir(s) 125,846,482,944 bytes free

The envoking of MSACCESS.EXE results in:

Does Access 2007 MSACCESS.EXE not support DB's with a space in the file name being spefied to auto-open via command line switches?

View 3 Replies View Related

Modules & VBA :: Import More Than 300 TXT Files And Add Filename Into Empty Field

Aug 14, 2015

I need to import 300+ individual .txt files into a MS Access DB table (named "tbl_input"). Each of the the .txt files have a naming convention similar to this:

Pink_Floyd_YYYYMMDD.txt

Because this initial import is so large and I would like to avoid doing it manually, I need to be able to:

import each of these files into a single table
delimit the text in each based on the "|" symbol
add the filename into an empty field

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved