import multiple txt files to different tables

[isladogs]

Ah I see...
Then in that case, you need to include that part as some kind of variable
e.g. a long integer called lngFolder

Obviously I have no way of knowing how you get that info.
Perhaps you can read that info from the folder name using VBA but how would it know where to look?
Otherwise you'll need to enter it manually

Dim lngFolder As Long
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & lngFolder & ".txt"

 

[lookforsmt]

Enter manually the txt file name each time , makes it manual process.

is there a way to read the later part of the txt file name, something like *.txt

i am only guessing

 

[lookforsmt]

Can this be done through function as i have on daily basis 4 to 8 text files with different names to import

 

[isladogs]

Yes

Lets say the file is called c:\myfolder\mysubfolder\myfile.txt

Then use this code

Dim strFileName As String
strFileName = "c:\myfolder\mysubfolder\myfile.txt"
strFileName = Mid(strFileName,InstrRev(strFileName,"\")+1)

The output will be 'myfile.txt' so your code becomes

Dim lngFolder As Long
path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") &strFileName

HINT:
Use the VBE Immediate window to work out code like this.
Its your very best friend ...

So in there type:
strFileName = "c:\myfolder\mysubfolder\myfile.txt" and press enter

Then type
strFileName = Mid(strFileName,InstrRev(strFileName,"")+1) and press enter

Then type this to see the result
?strFileName

UPDATE:
Just saw your last post
Yes - put the code in a function

 

[lookforsmt]

HI! Colin

I have done as you said below code is the function

Function ImportFileName()
    Dim lngFolder As Long

    strFileName = "c:\myfolder\mysubfolder\myfile.txt"
    strFileName = Mid(strFileName, InStrRev(strFileName, "\") + 1)

    End Function

and put the below code

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & "strFileName"

There is no error, but still the import count is zero.
but it

 

[isladogs]

But it what?

Remove the quote marks around strfilename
They shouldn't be there.

Now try the import again.

If it fails, do a debug.Print after the path1 line then see what is shown in the immediate window
Does the result match your file name?
If it does then there's a problem with your import code.

If it doesn't match the file name, tweak the code so it does match


Sent from my iPhone using Tapatalk

 

[lookforsmt]

Hi! i am getting below result in the immediate window

strFileName = "c:\myfolder\mysubfolder\myfile.txt"
strFileName = Mid(strFileName, InStrRev(strFileName, "") + 1)
?strFileName
myfile.txt

path1 = "E:\Import\Folder1\Daily download folder\ICCSC01001026" & Format(Date, "yymmdd") & strFileName
?path1
E:\Import\Folder1\Daily download folder\ICCSC01001026170915myfile.txt

i am clueless what it ment

 

[isladogs]

You just copied my example code without modifying it.
You've told it the file is called my file.txt so that's what its returning!

 

[lookforsmt]

Sorry, Colin i was thinking that this will the file which will get the file name in the folder.

If i put the file name in the code then how would i get the result if the following day the file name changes. Will i need to change the file name each time it changes

 

[lookforsmt]

i am just thinking, if there is a way to get the file names in new table which i can use for importing by way of a function.

 

[isladogs]

If there is an understandable logic to the file name, you can use that in your formula to get the new filename

However, you gave 3 examples, 111100, 111900, 111250 so I've no idea what the next one in the series would be

Alternatively, you could perhaps you other code to get the file name of the latest file added to the folder ... but that's a whole new thread in its own right

 

[lookforsmt]

Thanks Colin, the file last 6 digits have no logic and can be anything of 6 digits.

To get the file name in a new table. Should i open a new thread or can continue here itself.

I just tried to put some codes in the function, here i am getting a blank line in the new table "tbl_Files_Imported without the file name when i call the function

 

[isladogs]

You're in luck ... AGAIN

The code below will get the name of the latest file in a folder.
Add the code below to a standard module.
It has 2 functions

Function NewestFileInFolder(strFolder As String, strFileType As String)

'Usage - gets the latest file of a specified type in a selected folder
'Use "*.*" for any files; "*.txt" for txtfiles etc
'e.g. NewestFileInFolder ("c:\windows\system32\", ".txt")

Dim strFileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date

'check for trailing '\'
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"

'get files matching folder & file type
strFileName = Dir(strFolder & strFileType)

'find latest file
If strFileName <> "" Then
    MostRecentFile = strFileName
    MostRecentDate = FileDateTime(strFolder & strFileName)
    Do While strFileName <> ""
        If FileDateTime(strFolder & strFileName) > MostRecentDate Then
             MostRecentFile = strFileName
             MostRecentDate = FileDateTime(strFolder & strFileName)
        End If
        strFileName = Dir
    Loop
End If

'output latest file name
NewestFileInFolder = MostRecentFile

'Debug.Print NewestFileInFolder

End Function

Function NewestFilePath(strFolder As String, strFileType As String)

'Usage - gets the full path of the latest file of a specified type in a selected folder
'Use "*.*" for any files; "*.txt" for txtfiles etc

Dim strFileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date

'check for trailing '\'
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"

'get files matching folder & file type
strFileName = Dir(strFolder & strFileType)

'find latest file
If strFileName <> "" Then
    MostRecentFile = strFileName
    MostRecentDate = FileDateTime(strFolder & strFileName)
    Do While strFileName <> ""
        If FileDateTime(strFolder & strFileName) > MostRecentDate Then
             MostRecentFile = strFileName
             MostRecentDate = FileDateTime(strFolder & strFileName)
        End If
        strFileName = Dir
    Loop
End If

'output latest file name
If MostRecentFile <> "" Then
    NewestFilePath = strFolder & MostRecentFile
Else
    MsgBox "There are no " & strFileType & " files in the folder : " & strFolder, vbExclamation, "No files"
End If

'Debug.Print NewestFilePath

End Function

For the first function, in your case I think you would use this:

 NewestFileInFolder ("E:\Import\Folder1\Daily download folder\[B][COLOR="Red"]"[/COLOR][/B], "[COLOR="red"][B]*[/B][/COLOR].txt")

That should give a result similar to this:

ICCSC01001026170915111100.txt

So then you would modify your previous code like this....

path1 = "E:\Import\Folder1\Daily download folder\" & NewestFileInFolder ("E:\Import\Folder1\Daily download folder\[B][COLOR="red"]"[/COLOR][/B], "[COLOR="red"][B]*[/B][/COLOR].txt"

Once again, try it in the Immediate window adapting as necessary

BUT lets make it even easier...by using the 2nd function which outputs the full path of the latest file

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\[B][COLOR="red"]"[/COLOR][/B], "[B][COLOR="Red"]*[/COLOR][/B].txt")

That should give you EXACTLY what you need! i.e. the full path of the most recent file:

"E:\Import\Folder1\Daily download folder\ICCSC01001026170915111100.txt"

EDIT:
Corrected the above adding missing wildcard * and " (shown in RED) in the examples.
Apologies for the errors

 

[lookforsmt]

thank you Colin for going out of the way to help me
I will try the code and update

 

[isladogs]

You're welcome

All donations to my favourite charity ...

 

[lookforsmt]

Hi! Colin

i am really sorry, i have put the function code in two separate modules and then put the below code in the immediate window. But i am getting error"Expected error: line number or label or statement or end of statement"

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\,
".txt"

 

[The_Doc_Man]

I'm going to step into this mess and ask a different flavor of question.

What is the purpose of all of this? We see you importing data to tables but you then pretty quickly appear to erase them. You go through the trouble of importing data but then if you do a DELETE * on the table, it ain't there no more and all you have is an empty table. Further, you are creating tables but I don't see (from the discussion) where you do anything with them. Do the tables always exist and you just re-use them? Because if not, you will run into an Access limit on the number of simultaneous table objects that you may have (open or not).

What is the purpose of all of these gyrations? There may be a more elegant way that would help you learn a little VBA and - more importantly - learn to step away from the trees so you can take in the forest (so to speak). We certainly want to help you with your problem, but I try to remember the old Chinese proverb: Give a man a fish and you feed him for a day; teach a man how to catch fish and you will feed him for a lifetime.

There is absolutely NO disrespect intended in any of this. We ALL went through that big bump of learning how to bend these cranky machines to do our bidding, and I shudder to remember how clunky my first few programs really were. But... heck, everybody's got a learning curve. And many of us are experienced in helping others through the curve.

 

[jdraw]

Thanks Doc. I thought it was just me---kept asking myself why are you doing this?
I'd like to hear more about the purpose and why some discipline couldn't be added to the process to reduce the unknowns.

And you are bang on that we all went through some learning gyrations before some of it started to make sense.

 

[isladogs]

Doc Man has asked a number of questions that I should have done a long time ago. It would be very good to hear the answers now you've been prompted.

As for whether it's a 'mess', I'll leave others to decide ...!

But now I see jdraw asking the same question ....

Anyway, having spent so much time to get this far .... the direct issue is an unfortunate line break which I corrected but obviously after you used the code. Correct versions:

 NewestFileInFolder ("E:\Import\Folder1\Daily download folder\[COLOR="red"][B]"[/B][/COLOR], "[B][COLOR="red"]*[/COLOR][/B].txt")

path1 = NewestFilePath ("E:\Import\Folder1\Daily download folder\[B][COLOR="Red"]"[/COLOR][/B], "[COLOR="Red"][B]*[/B][/COLOR].txt")

EDIT:
Corrected the above adding missing wildcard * and " (shown in RED).
Apologies for the errors

 

[lookforsmt]

Thank you Sir (The Doc Man)
i have made it clear that i want to learn building a vba code and its for the simple reason that there are so many people here who are more the willing to help, Mr. Colins

To give you background of my project. I like to do small small automation at my work.
I have seen some of my colleague on daily basis spends almost 1 to 2 hours on daily basis first to convert the text file to excel and then balance the excel and then balance with other docs. This is prone to errors due to the value involved in the field.

I want to import the txt files to separate temp tables and then move them to the Master table and then delete the temp tables.
Because i wanted to import tables which are the latest files (i.e. todays date: yymmdd) but the file has the last 6 digits which keep on changing.

Currently Mr. Colin has helped me with the function code but i am stuck due to my poor knowledge in VBA.

regards
Mahesh