LinkBack URL
About LinkBacks
I have a notepad file and I want to extract some of the data in the notepad file to an excel spreadsheet. is this possible?
I have a notepad file and I want to extract some of the data in the notepad file to an excel spreadsheet. is this possible?
Yes. But you are not giving a whole lot of information. Is this something you only need to do occasionally? Are you talking about a plain old text file? Is the file always in the same location? Is it always named in a consistent format? Is there more than 1 file in the folder? These are just some things to think about, but you could start by recording a macro using the data import wizard and seeing what it does then tweaking as needed.
Greg
Just a guy trying to make work stuff easier.
so I want a form to have a textbox and when I type a part number in the textbox and hit enter I want excel to fine the note pad file that matches the part number entered. the file will always be in the same folder.
so lets say I enter 127196 into the textbox in user form I want it to look for the matching file name in the folder located : C:\Users\Joshua\Desktop\Partinfo
the text file looks like this
if you notice there are different sets of data... I want to find the most recent set of data based on the time stamp at the bottom of each set and when it finds the most recent set I want to look in the set for the numbers 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15. and when each number is found it copys that numer and the first number after the word DATA in each line for example ... using the data above the most recent set of data is at the bottom and number "1" is found in one of the rows and the first number after data is ".388 D" then number "2" is found and the first number after "DATA" is ".441 D" and so on...
Please Login or Register to view this content.
and I would like it to copy into the excel sheet
I know this is a lot of information and it might be a little confusing! I tried to explain it the best I can! thank you!
Any ideas? I'm stuck
Hello Jenkins27,
This workbook should be a good start. It will look for a text file in the directory "C:\Users\Joshua\Desktop\Partinfo". If it is found then the data will taken from the file an displayed in the ListBox on the UserForm.
Macro to Locate Text File and Return the Data
Code for the Command Button on the UserFormPlease Login or Register to view this content.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thank you but I think there was a little misunderstanding.. I didn't want the data to be in a listbox.. I wanted the data to be displayed in the spreadsheet! Thanks for your help
Would it not be better to use a combobox and fill it with the TXT files in thisworkbook.path. One could alternatively open that folder in a dialog with TXT files listed. The user can then more readily pick the file.
When you say look for the most recent date, are you sure that the file was not created with appended blocks of data? The last data block would then be the last block. IF that is the case, coding is not that difficult. If not, more work is needed.
To get the best help, help us help you. You can do that by zipping a simple TXT file and a simple Excel file where you manually input what is expected. Then attach that file by clicking the Go Advanced button in lower right of a reply, click the paperclip menu icon, and then browse and select the ZIP file and upload.
okay I have 2 attachments one is the excel file and the other is the text file... so what I want is when the form comes up and and asks for the part number I want it to look for the matching file and take the most recent data ( which is always at the bottom of the text file) and in the excel sheet I put in red how I want the data to come over so looking at the data you can see which values I want to come over I just put a couple in there so you had and idea but id like all 15 to come over.
when each number is found it copys that number and the first number after the word DATA in each line for example ... using the data above the most recent set of data is at the bottom and number "1" is found in one of the rows and the first number after data is ".388 D" then number "2" is found and the first number after "DATA" is ".441 D" and so on...
Hello Jenkins27,
The only change I made was to the UserForm CommandButton code.
Please Login or Register to view this content.
thank you! I also keep getting a subscript out of range runtime error 9 in the other code seen in red... any ideas whats wrong there?
Please Login or Register to view this content.
I made two changes to Leith's code. (1) Used ThisWorkbook.Path rather than the hard coded path. (2) Deleted the data from row 3 and down rather than row 3 and up for a 2nd run. Speaking of a 2nd run, do you intend to run this many times and just append the data to the next empty row after row 2?
I am not sure why Leith used Redim there. It can only redim the outer dimension but not the others. I did not see that error in any case.
Please Login or Register to view this content.Please Login or Register to view this content.
thank you that worked great for the 127196 part but not the other text files in the folder! I do want to reuse this and go down to the next available line! any ideas why it only works for that one file?
okay I got it working for all the files but now if I could just get it to append the data to the next empty row after row 2 that would be perfect! thank you
Please Login or Register to view this content.
awesome! is there a way to make it so it will find the file aslong as the partnumber is in the name? because sometimes the files are saved with a "rev a" or "rev d" at the end of the part number they always change.. so that would be very helpful thank you
Last edited by Jenkins27; 02-28-2015 at 05:31 PM.
but they are always in the same folder path
For the method you have used, it could only find the first one.
Like I said, you might want to add combobox to drill down to it or a dialog call. Of course you might also want to think about selecting several rather than just one at a time if you might ever have that need. All 3 methods can handle that.
Last edited by Kenneth Hobson; 02-28-2015 at 06:09 PM.
thank you!
Last edited by Jenkins27; 02-28-2015 at 09:46 PM.
thank you and id like it to just be one at a time... and im trying to keep it simple for work.. so if there is a way that I could just type the part numer in and it finds the file with that number in it.. that would be the best fit for me! thank you
As I said, if you want that, can you live with just getting the first match?
e.g. Use Dir() with the drive:\path\filename*.txt. i.e. A wildcard of * or ? to find the first one.
yes that's how I would like it.. find the first one. could you show me in my code how to put it?
Please Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks