Export CSV data to Excel Userform using reference number to specify relevant data

[Sc0tt1e]

I have a Notepad saved in a folder which I keep notes everytime I speak to a customer.

What I would like to do is create a userform where I can input a case reference number and click a command button which searches the CSV/TXT file for any notes for the specific reference number and then returns all notes (seperated by a blank line) in the textbox within the userform in the posted/saved format.

I can find lots about importing to excel but nothing about importing data to userforms. Is what I am attempting possible and if so what should I be googling/researching to assist me in doing so?

CSV file added for format. The userform contains two text boxes, one blank for import and one for completing the case reference number (format on CSV file. Line item 1).

i.e. if I searched by SL-001-0155648 I would get the below (It's not code but I thought the best way of showing what I mean):

Please Login or Register  to view this content.

[Tinbendr]

You can use the standard input output commands available in VBA.

e.g.

Please Login or Register  to view this content.

The trick here would be to Test for the ref#, then skip or add to the Temp$ for final assignment to the textbox.

[Sc0tt1e]

I'm afraid I have no idea what this code is doing?

[Tinbendr]

Each line input reads a single line in the text file.

Sent from my phone using Tapatalk.

[Leith Ross]

Hello Sc0tt1e,

The attached workbook has a UserForm as you described. Save this in the same folder as the CSV/TXT files. If you need to be able to select a folder, let me know and I will change the code to do so.

The macro will open each file in the folder and search it for a matching reference case number at the beginning of each line. The end of the matching note is either a blank line or the end of the file.

All matching notes will be displayed in the second text box.

Here is the code for the UserForm.

Please Login or Register  to view this content.

[Sc0tt1e]

This document won't be saved in the same location as the CSV file so it will need to be referenced in the code, can you assist with that as I'm still breaking the code down to understand it?

[Sc0tt1e]

Ive just run the code and it has created hundreds of files in my drive???

[Tinbendr]

Very nice Leith!

However, I couldn't get it to work for the longest. I discovered that the open had to have the full path since I create a subfolder under the ThisWorkbook.

Please Login or Register  to view this content.

I would have thought that oFile would be the full path and filename.

Even oFolder only has the last subdir.

[Leith Ross]

Hello Sc0tt1e,

The code below can be altered in two ways. One is to used a fixed folder path in the code. Two is to let the User select the folder. The code below is set to the first option, using a fixed path. The second option is present but commented out.

The folder is set to "C:\Users\Owner". Change this to where your files are located.

Replace the code for the CommandButton1 in the UserForm with this code or just use the attached workbook.

Please Login or Register  to view this content.

[Tinbendr]

This still doesn't work without the full path in the open statement.

oFile is just the filename.

oFolder is just the last subdir.

David
Win7-Office 2010

[Leith Ross]

Hello David,

The Shell object returns a Folder object for the Folder path specified or Nothing if the folder can not be found. The Shell does not care if the folder path ends with a backslash or not.

The files contained in the folder are returned and then filtered by type either csv or txt. No other file types are returned from this filter. If none of these types are returned in the filter then Nothing is returned.

Each file is then read in as a text string, converted into an array of individual lines and checked for the reference number.

So, I don't know what you did but the code does work.

EDIT: David, I did discover an error in the code allowing the user to select a folder. Is that where your error occurred?

[Tinbendr]

Hmmm... I don't know what's going on either.

So my path is.

Please Login or Register  to view this content.

I know this is a valid path since I copied it from the Address bar of Windows File Explorer.

and then when I pass the line

Please Login or Register  to view this content.

oFolder only contains "Test".

Even if I use

Please Login or Register  to view this content.

to navigate to the folder, the code will error at

Please Login or Register  to view this content.

because FolderPath is just "Test".

oFile never shows anything except the filename.

I dunno?

[Leith Ross]

Hello David,

The behaviour you are seeing is normal. The default for a Folder object is the folder name. To see the full path use oFolder.Self.Path in your code.

Try using the code in post #9. This is the code will work with either a fixed path or one the user selects.

[Sc0tt1e]

I'm getting the same error at the same place?

I'm using the code from post 9 unedited except for the file path

Please Login or Register  to view this content.

If I add.txt to the end then

Please Login or Register  to view this content.

is the line that produces the error.

I don't know enough about this code to debug it

[Sc0tt1e]

I can't seem to get the code to work, I have uploaded the full doc.

The code is in userform "Tasks" (Tab 2 "Notes"), Sub Command Button3 click

Code is run from Userform Tab of Excel "Tasks & Notes" button

[Tinbendr]

When I change this line, it works for me, Scottie.

Please Login or Register  to view this content.

And you have to change to path to exclude the file name from the path.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Tinbendr]

As far as you Tasks userform, would you prefer to load all the case files reference #'s from Database Column B into the combobox? Instead of having it broken up? It's a reality simple task to load all the ref#'s.

[Sc0tt1e]

Bump for assistance

[Sc0tt1e]

Apologies, hadn't seen the 2nd page with further posts.

This is a template based on one user, there will be multiple users with the references added to the combobox (SL-100320-,DE,100320-,EH-100320- etc) so it works fine for now. Each person will have an auto generation for the last 3 digits based on number of files allocated to them. The code I have doesn't quite do what I need it to do but another problem for another time lol.

I've made the changes and it is creating a new folder and not picking up on the existing folder. I notice the

Please Login or Register  to view this content.

in the code, I need this to be a create or append, my understanding is that Unicode overwrites the data. Is this correct and is this what the code is doing?

Please Login or Register  to view this content.

[Sc0tt1e]

Please Login or Register  to view this content.

I am using the above piece of code to add data to the CSV file but anything posted has an " at the beginning of every line. This is preventing the code provided by Leith Ross from finding any matching RefNum's.

Can anyone suggest a change to my code to prevent this (which I am guessing is the preferred method) or a change to Leith's code to account for this (I tried adding """ before the refnum but it didn't like it and expected a Go To)?

[Sc0tt1e]

bump for assistance

[Leith Ross]

Hello Sc0tt1e,

After looking at the last workbook you posted, I noticed what may be the cause of the macro failing. It appears you are working with a single text file "H:\My Documents\CSV\Notes Test CSV.txt".

The macro was setup to read all txt/csv files in a specific folder and search each one and extract all notes that contain the reference number. It can be changed to search a single file if that is what you want.

[Sc0tt1e]

The format will ony ever be .txt.

Would it be possible to show me both so I can adapt the code depending on how we decide to run this operationally (comment one out for now) please.

For now this is just me building a template but once it is completed it will be used by multiple users (each person with their own form sending data to one database. I'm not sure if there will be folders for each user with a txt file within it, or if there will be one txt file which everyone will post to.

Suggestions welcome

Also, any chance you know how to get rid of the quotation marks the code posted in "Post 5" is producing in the .txt file?

[Leith Ross]

Hello Sc0tt1e,

I will write the code for both and leave one commented out (the multiple files). I will looking into the quote problem as well.

[Leith Ross]

Hello Sc0tt1e,

I have rewritten the macro. You can now use a file or folder path. If a folder path is used then all text files in the folder are searched for notes matching the reference number.

The file path is now validated. If the file or folder can not be found, a message box is displayed to alert you and the macro exits.

An added option is to let the user pick the file to be opened. This Line is commented out.

Here is the new macro code. This has been added to the new workbook and tested.

Please Login or Register  to view this content.