imprting file with oldest date (1 Viewer)

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
I have a database which imports data from a text file does some conversions & reports and exports to a text file. What i need to do is instead of specifying the filename, is to tell it to get the oldest file in a folder as the import file. Then when the conversions have been done, move the file to another directory, can someone give me a clue
(BTW I 'm a complete newbie to vba, so go easy on me
)
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
Hi jh,

i'm assuming you've got the import automated okay so if not post back. This is not very tidy but it does work so post the GetTheOldestFile function into a module and you can call it as i have in in sub h ( but hopefully with a more usefull outcome! ). As far as renaming/moving the file goes you should really do that once you have the import done, then just check "Name Statement" in the help files,

HTH

Code:
Sub h() 
    dim strTheFileName as String
    strTheFileName=GetTheOldestFile("H:\My Documents", "*.txt")
End Sub


Public Function GetTheOldestFile(strPath As String, strType As String) As String
    Dim strFile As String
    Dim dtmDate As Date
    Dim x As Integer
        
    dtmDate = Date
    With Application.FileSearch
        .NewSearch
        .LookIn = strPath
        .FileName = strType
        If .Execute() > 0 Then
            For x = 1 To .FoundFiles.Count
                If FileDateTime(.FoundFiles.Item(x)) < dtmDate Then
                    strFile = .FoundFiles.Item(x)
                    dtmDate = FileDateTime(strFile)
                End If
            Next x
        End If
    End With
    
GetTheOldestFile = strFile

End Function
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
Erm. no i haven't got the import automated, at the minute i just use a macro with a transfer text, import file.
(i've not really used vba before and my boss has said that he wants this doing in 50 minutes time !)
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
tell him to get u a training course, who the hell does he/she think he is. "here just program a db for me - but don't expect any help, you can use that internet thingy, right?" bloody PHBs. Anyhow, rant over
but if your gonna enjoy that job i'd guess you'll probably need to spend some money on books and do it off your own back. It's worth it, if only to see the look on their faces when all their processes depend on the stuff you wrote and you hand in your notice <big grin, that'll be an extra £xK please>

to do it in code is really similar to doing it a macro, check out the TransferText action/method of the DoCmd object in the help files - it's only one line of code. Then you just need to substitute
GetTheOldestFile("FolderToGetFilesFrom","TypeOfFileToGet - eg*.txt") in the place of FileName on the DoCmd.Trans... line

Follow that line with the Name statement above and you should have imported and moved your file!
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
Yeah tell me about it. I wasn't the happiest i can tell you. especially as i have a couple of other prjects which also have to be done asap. oh well
Any thanks very much for this greately appreciated.
Now for the real idiot question, how do i go about using the code? i assume i use the RunCode option from within a macro. But when i goto the module that i have created the code there are no defined functions.
can i just type the GetTheOldestFile(..) in the code window ??(sorry i know this is simple stuff, but as i said, never really done vba before)

Thanks very much indeedy sir
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
i am assuming it is fine to use network paths as filepaths, eg. \\servername\folder\file *.txt
SO i've got DoCmd.TransferText.... when i use GetTheOldestFile( do i put in the folder i want it to look in, in the parenthesis?. eg. GetTheOldestFile("\\server\folder", "*.txt")

thanks
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
sorry, i'm really not the best at giving complete instructions! Okay, so you should have th whole of the GetOldestFile function in a Module.

Then my personal choice would be to make a form and put a button on it. Go into the properties of that button ( right click | properties or double click ) and go onto the events tab, select on click and Event Procedure, hit the dots to the right. You should now have something like Private Sub Command1_Click()

End Sub

(hopefully) Into that place the calling code
Code:
  DoCmd.TransferText ...etc
  Name lala As Po

Save the form, open it up, hit the button and it should work??? Good luck, i wholly sympathise with u, stick with it tho' it's well worth it


EDIT - yup, you should be fine using server paths, the way you're calling it looks fine

[This message has been edited by KDg (edited 09-28-2001).]
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
OK i have done this
I copied & pasted your code into a module. This is my code,
Private Sub Command5_Click()
DoCmd.TransferText acImportFixed, "Import", "tblImport", GetTheOldestFile("\\capt_kirk\conversion\data\caa", "*.txt")

End Sub

When ever i try running it i get "Compile error, sub or function not defined" with the GetOldestFile highlighted.
I've gone through the help suggestions of why this might happen but got no joy.
Where am i going wrong ??
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
just figured out where i was going wrong, the formatting of your code was slightly messed up in the copy & Pasting.
Now i get a different error, The action or method requires a FileName argument
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
hmm, i've copied my code out and copied you docmd and its working fine over here. Try putting a break mark ( click on the right hand grey bar in the code view ) next to you docmd line. Then go to view the form, click the button and the code should stop on the line u marked. Press f8 to go thru the code line by line, it should take you straight into the Getxxx function, at the moment the function will return "" if no files are found in the specified folder so you may need to do some error handling. Just make sure that it gets at least as far as the "For x = 1 To .FoundFiles.Count" that means that is has found some files, if it jumps over it, check that the location you are using does have some .txt files in it. If that's not it post back again

[This message has been edited by KDg (edited 09-28-2001).]
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
its very strange, there are files in the folder, and the script finds them. I have moved another file into the folder, and it works. The files that were there were all created today, the file i copied was older.
It works with the older file in there but not the newer file.
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
just tried changing the system clock. It seems to not like files created on the same date as the system clock. (i assume this is because the < dtmDate is the system clock ??).
Any suggestion on how to get around this?
It works fine other than this problem.
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
aaah, that would be my lazy coding - if you change dtmDate=Date to dtmDate=Now then it should all work just fine ( no, really, this time it will...)

hey, your diagnosis is pretty good for someone that's never coded before. Get that book and you'll be laughing in no time. The dtmDate is, as you say, set to the system date at the start of the function, it's then changed to match each older file as it goes. changing it to Now will mean that any files made over ( what, one second old? ) will be checked.

[This message has been edited by KDg (edited 09-28-2001).]
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
Brilliant, works a treat, thanks very much for all of your help this has been most appreciated.

Thanks for the comment too, i think it helps that i do php coding so i can just about grasp what a piece of code does (it helps when variables & stuff are well labelled like yours, not like the windows help!)

Anyway thank you very much, i think i feel brave enough to attempt the moving of the file by myself. (BTW inorder to get this file name again, would i have to run the GetTheOldestFile again or could i put this value into a public variable.)
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
ooh php - that's what i'm getting up to speed on at the moment - it's just so damn nice and powerful. I got the php cookbook and it's excellent, i really wish there was a vb equivalent.

As you say it would be more efficient to call Getxxx once and keep it in a var, but it doesn't need to be public. You can declare it in Private sub Command... as
dim strFileName as string or whatever and then just use that.

Best wishes,

Drew
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
What i want to do is to use the same filename in a different function, because after the converted file has been converted it needs moving, so rather than working out the oldest file and possibly getting a different answer, i thought that the best thing to do would be access the same variable. Can i do this buy just declaring the Dim strfilename As String, in the export function ? or do i have to do something different?

cheers

[This message has been edited by jesus_hairdo (edited 10-02-2001).]
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
if you did set up a button on a form then do it in there eg
Code:
Private Sub Command492_Click()
  Dim strFileName as String
     strFileName=GetTheOldestFile("xyz","*.txt")

  if Len(strFileName) then
     DoCmd.TransferText acImportDelim, "spec", "table", [b]strFileName[/b], False / True
     Name [b]strFileName[/b] As [i]NewNameAndPlaceToPutIt[/i]
  else
    msgbox "didn't find anything..."
  end if
End Sub

[This message has been edited by KDg (edited 10-02-2001).]
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
i don't quite follow what you've done here.
I 3 3 buttons on my form, 1 imports data & cleans it up, 2 prints out reports and 3 exports the data. so i want sub on button 3 to access variables set by button 2.
Here is my code (the 2 starred lines in button 3 are the lines that i want to be able to reference what button 1 set.

This is button 1 code,
Private Sub Command5_Click()

Dim filename As String, filepath As String
Dim msg_folder_notexist As String
Dim import_check_response As String, import_check_style As String, import_check_msg As String, import_check_title As String
Dim bad_import_msg As String, bad_import_title As String, bad_import_style As String

bad_import_msg = "Please contact Jamie x2038 immediately"
bad_import_title = "Import failed"
bad_import_style = vbOKOnly + vbApplicationModal + vbCritical
import_check_msg = "Does this look OK?"
import_check_title = "Data import check"
import_check_style = vbYesNo + vbQuestion + vbApplicationModal
filepath = "\\capt_kirk\conversion\data\caa"
filename = GetTheOldestFile(filepath, "*.txt")
Set fs = CreateObject("Scripting.FileSystemObject")
msg_folder_notexist = "The folder " & filepath & " does not exist Please contact Jamie on x2038"

If fs.FolderExists(filepath) Then

If filename = "" Then

MsgBox prompt:="There are no more files to Import"

Else

DoCmd.TransferText acImportFixed, "Import", "tblImport_Data", filename
DoCmd.OpenQuery "qryExport_Data"
import_check_response = MsgBox(import_check_msg, import_check_style, import_check_title)

If import_check_response = vbYes Then
DoCmd.Close acQuery, "qryExport_data", acSaveNo


Else

import_check_response = MsgBox(bad_import_msg, bad_import_style, bad_import_title)

End If


End If

Else

MsgBox prompt:=msg_folder_notexist

End If

End Sub

this is button 3 code,

Private Sub Command6_Click()
Dim filepath As String, filename As String
Dim exportfilepath As String
*filepath = "\\capt_kirk\conversion\data\caa"
*filename = GetTheOldestFile(filepath, "*.txt")
Set fs = CreateObject("Scripting.FileSystemObject")
exportfilepath = filepath & "\done\"
exportpath_notexist_msg = "The export path doesn't exist. Please contact Jamie x2038 immediately"
exportpath_notexist_title = "Error"
exportpath_notexist_style = vbOKOnly + vbApplicationModal + vbCritical

If fs.FolderExists(exportfilepath) Then

fs.MoveFile filename, exportfilepath
MsgBox prompt:="File exported"
Else

MsgBox exportpath_notexist_msg, exportpath_notexist_style

End If


End Sub
 

KDg

Registered User.
Local time
Today, 12:27
Joined
Oct 28, 1999
Messages
181
Hi again,

first you can get filepath = "\\capt_kirk\conversion\data\caa" that you use in both sections of code into one. Up the top just under the Option Compare Database
Option Explicit you could use
Private Const strFilePath as String = "\\capt_kirk\conversion\data\caa" . Then you can call it from anywhere in the form and only have to maintain it in one place. If you declare strFileName in the same way ( Private strFileName as String ) then once you've got its value in button 2 it will be available in button 3. You'll need to remove your local declarations ( Dim filename As String, filepath As String ) otherwise you'll either get errors or the local var will be used and you won't get the value of the form level var set.

hmm...anything else...Are you sure your users will always hit button 2 before 3? if not then strFileName will be empty. If you're worried about the files moving/disappearing while the code is running ( probably a good thing to worry about ) then check out Dir in the help. As an aside std vb var naming is normaly a 2 or 3 letter prefix to the var name giving its type eg str for string, int for integer, lng for long etc etc, that can be handy when you come back to look at your code later. Some books only use a 1 letter prefix... That's all i can think of for the mo

regards

Drew
 

jesus_hairdo

Registered User.
Local time
Today, 12:27
Joined
Sep 28, 2001
Messages
32
does the Private Const strFilePath as String = "\\capt_kirk\conversion\data\caa" bit go in the module part before the sub h() (declarations section )

on a side note how do i do this,
if file1="" or file2="", do i just use or, or is it | | like in php, or not even possible using vba? what if i wanted to do an AND (&& or AND ?)

I am most greatful for this.
 

Users who are viewing this thread

Top Bottom