Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Old 12-31-2015, 08:41 AM   #1
Newly Registered User
Join Date: Jun 2015
Posts: 9
Thanks: 0
Thanked 1 Time in 1 Post
JoeGKushner is on a distinguished road
Best Way To Import a Variable File?

On a daily basis, I am importing a file whose name changes every day. It's based on when the time the creation date for the file finishes so it's DDOPHEE_201512310613.TXT one day and DDOPHEE7_201512310620.TXT another day.

I was wondering if I could use VBA through a Module to look in the specific folder/location, which never changes, and use the File system object properties, to grab the newest TXT file, if it matches today's date in terms of it's Date created properties, regardless of it's name?

I've seen some code that looks like it might cover a bit of this but am not sure if it would do everything I'm looking for. I know I'd have to change the folder location for example and some other bits. Any feedback?

Dim objFile As File
Dim objFolder As Folder
Dim objFSO As FileSystemObject
Dim FolderToScan As String
Dim NewestFile As String
Dim NewestDate As Date

Set objFSO = CreateObject("Scripting.FileSystemObject")

FolderToScan = "\\\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

FileCopy NewestFile, "\\\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt"

CurrentDb().Execute "DELETE * FROM tImport", dbFailOnError
DoCmd.TransferText acImportDelim, "tImport Specification", "tImport", _
"\\\RSH_Log\DATA\4300R TEST DATA PLOTS\import.txt", False

MsgBox "Data has been imported into the tImport table"

JoeGKushner is offline   Reply With Quote
The Following User Says Thank You to JoeGKushner For This Useful Post:
mveijndh (01-02-2016)
Old 01-01-2016, 07:42 AM   #2
Peter Reid
Newly Registered User
Join Date: Dec 2003
Location: Somerset
Posts: 132
Thanks: 11
Thanked 20 Times in 19 Posts
Peter Reid is on a distinguished road
Re: Best Way To Import a Variable File?

Yep, it looks like everything is in that code for you to do what you've asked.

Why not make your changes and try it and let us know the outcome?
Peter Reid is offline   Reply With Quote
Old 01-04-2016, 09:38 AM   #3
Newly Registered User
Join Date: Jun 2015
Posts: 9
Thanks: 0
Thanked 1 Time in 1 Post
JoeGKushner is on a distinguished road
Re: Best Way To Import a Variable File?

Okay, after some messing around, I've managed to get it to work to a point that I'll have to fine tune the actual import steps to verify that the file is similar enough every day that it doesn't decide half of what's in there is garbage.

Thanks for the extra eyes everybody!

If someone can mark this as solved? Is that something I can do?

EDIT: Okay, now to the next problem, I only want it to look at specific files starting with the first 4 characters. Any ideas?

Last edited by JoeGKushner; 01-04-2016 at 11:54 AM.
JoeGKushner is offline   Reply With Quote

import , variable data , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Check to see if import file has matching table field before import ccondran08 General 2 11-13-2015 02:16 AM
Import txt file to SQL Server using Bulk Insert and a Format file PeterOC SQL Server 1 09-12-2008 09:09 AM
Text file to MS Excel multiple file import application tokunbo General 3 07-22-2006 11:08 PM
Create an import specification on a variable text file??? NJudson General 8 11-22-2004 08:15 PM
Import using Variable rschultz Forms 3 12-11-2001 09:34 AM

All times are GMT -8. The time now is 04:11 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World