Good afternoon,
I am trying to run a script that will open a .txt file where only part of the file name is known. My code is below. The Range below, NHCUopenFilePath refers to the file location C:\TempPath\NHCUPaymentfile06272018*.txt The * denotes the place where 10 random digits will appear on the .txt file that needs to be opened. When I run this code below, I'm receiving a runtime error 1004, unable to find file path.
Please Login or Register to view this content.
Doesn't work that way.
I suggest you record a macro to open ONE of the files (it's a text file) and import it in Excel.
After that edit the macro do loop though
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Yes, but how does the macro open the text file if the name is partially unknown?
The Dir Function is usually used to loop through files in a folder and you can use the "*" character for a wildcard. No loop is required in your case. Try this...
Please Login or Register to view this content.
That code segment is still giving me the same runtime error. Copying it in below for the full version. 2018-06-27_15-25-36.jpg
Is the full name of the file being passed? By that I mean, Dir needs to be given the file path & file name.
Not totally sure what you're asking. The full name (minus the last 10-14 digits) and path are expressed in the NHCUOpenFilePath range.
@Seth_ can YOU please attach the macro code you are using?
I'm not going to try an guess and asume what you're doing without a clear view of the code you have.
My opening post contained the code I am trying to use. Here again.
Please Login or Register to view this content.
Sorry, that was yours. Here's mine...
Please Login or Register to view this content.
Just so we're clear, can you please copy and paste the value of the NHCUOpenFilePath range in a reply here?
C:\TempPath\NHCUPaymentfile06272018*.txt
My code may also be totally bogus, so don't necessarily feel chained to using in in your reply. It's just the most recently iteration of my attempt to get this thing to work.
What's in Range("NHCUopenFilePath").value)) ?
Like I said I'm not going into guessing and assuming.
You either give a clear and concise explanation of what you want.
Which folder are you looking into
The prefix of the textfile (minus the 10 digits)
all you can come up with to explain to somebody that has absolutely NO idea what you're trying so that he can understand you.
And like I mentioned.
If you take the time to record a macro to import one text file into Excel then you have an idea how that works and is not with Workbooks.Open because a txt file is NOT a workbook
I'll read your answer tomorrow it's bed-time here
C:\TempPath\NHCUPaymentfile06272018*.txt is the value of the range and that's not good.
Will get back tomorrow.
I gave a clear and concise explanation in my original post; I'm not into repeating myself, but to give you the benefit of the doubt here it is again. I am trying to write a macro that will open and import data from a .txt file where the name of that .txt file is not fully known. The first 23 digits are known, but the last 10-14 digits are random. I was trying to use the wildcat (*) designation to get some code to work but it wasn't happening. Forget the range reference if that's confusing as it's not important to the core issue. Is there some code that will consistently open and import data from a .txt file where the last 10-14 digits are randomly changing from day to day?
Seth, I tested my code. Sorry. Looks like Dir returns only the file name even though the you provide the full path & file name (w/wildcard) in the argument.
Try this...
Please Login or Register to view this content.
Yeah, I read the first part and yes you want to raed (open) the text file and import the contents in the Data worksheet.
Do you wan to do that for all the txt files where the prefix is in this case a text and todays date, all in the same sheet?
If you suse cells you take the entire sheet and so will have many empty rows.
So like I said, be concise.
I understand that the file prefix consists of two parts.
One constant : NHCUPaymentfile
The second part the daye in the format mmddyyyy
the file extension is .txt
and in between a numeric string with 10-14 numbers.
Correct so far?
You want to read the contents of each text file in the "Data"worksheet. Correct?
Just one thing, I don't think you considered the sort order when viewing the file NHCUPaymentfile06272018 will not sort nicely because in 2019 NHCUPaymentfile06272019 will come right behind it.
Consider NHCUPaymentfile20180627 to keep years and months together.
Try change this value toOriginally Posted by Seth_
C:\TempPath\NHCUPaymentfile06272018##########.txt
That won't work!
Seth_
Can you just give me the sample of file names that you want to match to?
Only you know the real file names.
This code opens all txt files in the requested folder
You haven't answered the real questions so the processing of the text file may need correction (by you)Please Login or Register to view this content.
If * part is another 10-14 digits numbers after 06272018 then try
Please Login or Register to view this content.
@Jindon: took you a while
Now the OP has gotten two answers and yours takes care of the import in "Data"
Only OP knows. Not you.
Seth_,
If you can post few examples of file names showing the numbers at the end, it will be clearer.
You don't need the extra numbers of the filenames if you use Dir correctly, see my code and integrate with your part
Please Login or Register to view this content.
So inserting @Jindo's part of the code the macro is like this and works perfectly.
I created here a number of text files with the same prefix and random text and numbers after that an all are consecutively imported.
There is no check to see if the file has aldready been imported so if you run the macro more than once for the same string you will get duplicated imports but that is a question of using your own imagination and program that.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks