By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Get most recent file in a folder and then import it with transfertext into a table.

P: 4
I have the transfer text portion working fine, and I can select the most recent file in excel vba, but I can't get the most recent file portion of the code to work in access vba.

It ends with error "compile error variable not defined"

I'm a bit new at vba, could anyone help?


Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Set objFSO = CreateObject("Scripting.FileSystemObject")

FolderToScan = "\\10.3.0.144\RSH_Log\DATA"

Set objFolder = objFSO.GetFolder(FolderToScan)

NewestFile = ""
NewestDate = #1/1/1970#

For Each objFile In objFolder.Files
If objFile.DateLastModified > NewestDate Then
NewestDate = objFile.DateLastModified
NewestFile = objFile.Name
End If
Next
FileCopy NewestFile, "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"
CurrentDb().Execute "DELETE * FROM tImport"
DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False
MsgBox "Data has been imported into the tImport table"
End If
Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
Jan 21 '10 #1

✓ answered by ADezii

Try:
Expand|Select|Wrap|Line Numbers
  1. Dim objFile As File
  2. Dim objFolder As Folder
  3. Dim objFSO As FileSystemObject
  4. Dim FolderToScan As String
  5. Dim NewestFile As String
  6. Dim NewestDate As Date
  7.  
  8. Set objFSO = CreateObject("Scripting.FileSystemObject")
  9.  
  10. FolderToScan = "\\10.3.0.144\RSH_Log\DATA"
  11.  
  12. Set objFolder = objFSO.GetFolder(FolderToScan)
  13.  
  14. NewestFile = ""
  15. NewestDate = #1/1/1970#
  16.  
  17. For Each objFile In objFolder.Files
  18.   If objFile.DateLastModified > NewestDate Then
  19.     NewestDate = objFile.DateLastModified
  20.     NewestFile = objFile.Name
  21.   End If
  22. Next
  23.  
  24. FileCopy NewestFile, "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"
  25.  
  26. CurrentDb().Execute "DELETE * FROM tImport", dbFailOnError
  27. DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", _
  28.                   "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False
  29.  
  30. MsgBox "Data has been imported into the tImport table"

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
Try:
Expand|Select|Wrap|Line Numbers
  1. Dim objFile As File
  2. Dim objFolder As Folder
  3. Dim objFSO As FileSystemObject
  4. Dim FolderToScan As String
  5. Dim NewestFile As String
  6. Dim NewestDate As Date
  7.  
  8. Set objFSO = CreateObject("Scripting.FileSystemObject")
  9.  
  10. FolderToScan = "\\10.3.0.144\RSH_Log\DATA"
  11.  
  12. Set objFolder = objFSO.GetFolder(FolderToScan)
  13.  
  14. NewestFile = ""
  15. NewestDate = #1/1/1970#
  16.  
  17. For Each objFile In objFolder.Files
  18.   If objFile.DateLastModified > NewestDate Then
  19.     NewestDate = objFile.DateLastModified
  20.     NewestFile = objFile.Name
  21.   End If
  22. Next
  23.  
  24. FileCopy NewestFile, "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"
  25.  
  26. CurrentDb().Execute "DELETE * FROM tImport", dbFailOnError
  27. DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", _
  28.                   "\\10.3.0.144\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False
  29.  
  30. MsgBox "Data has been imported into the tImport table"
Jan 22 '10 #2

ADezii
Expert 5K+
P: 8,597
One very important note that I missed is that: You must set a Reference to the Microsoft Scripting Runtime Object Library in order for this code to work.
Jan 22 '10 #3

P: 4
Thank you, the Reference and the code were both very helpful.
Jan 26 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.