LinkBack URL
About LinkBacks
in my first attempt to solve this problem I believe I asked in a too complicated way.
For that reason I removed the question here and restated it in a post below.
in my first attempt to solve this problem I believe I asked in a too complicated way.
For that reason I removed the question here and restated it in a post below.
Last edited by dschmitt; 05-20-2010 at 05:16 AM.
I am now busy with another project. I will get back to this one in a few days.
Thanks for your help.
Now I have the time to get back to this problem. And I want to try a new approach.
I have a macro that can import several XML files. I want to change the macro so that it can import instead several comma delimited text files.
To illustrate I attached an Excel file called example.xlsm which contains the macro. I also attached 2 xml files. Please change the the file names from .txt to .xml. With these xml files you can test the macro. You can execute the macro by clicking on the AuthorAnalysis button on the far right in the Home menubar. This macro is working fine.
I also attached 2 comma delimited text files (1.txt; 2.txt). These are the files that I want the new macro to import.
I tried to solve this problem by first using the macro recorder followed by using the macro recorder code to modify the xml import macro. Unfortunately I was not successfull.
Last edited by dschmitt; 05-20-2010 at 10:01 PM.
boiling down the problem to the essential ...
how would I have to change the following code so that it will import a comma delimited text file?
Please Login or Register to view this content.
below is the code of the entire macro.
Please Login or Register to view this content.
I am trying another approach to get a solution to my problem.
Attached is an Excel file with the macro and one comma delimited text file.
The following code gives me an error in the first line of the Import file code
Please Login or Register to view this content.
Last edited by dschmitt; 05-20-2010 at 10:07 PM.
Hi dschmitt
I havent had a look at the file but the basic code to import text files
Please Login or Register to view this content.
pike, if you want to help then please look at the problem in my last post, which is the one before yours. I think if I can get a solution to this problem then I can do the rest.
I like my way better as it will let you sort values ect.
dont know what in the txt files but try..
Please Login or Register to view this content.
Last edited by pike; 05-20-2010 at 04:18 AM.
dschmitt,
Your problem was that you were attempting to pass an array of values (file names to import) to a procedure that can only process one file at a time. The solution is to add a loop, so that each value in your array gets processed. I attached an example of a loop that will import multiple files into a separate worksheet. However, let me be honest upfront and admit that I was lazy, and included no error handling procedure for dealing with a data array (number of files to import) that is greater than the number of worksheets in your workbook. It's late and I was concerned with resolving your original problem.
I hope this helps. Try running the attachment with the two attached text files (your text file 1.txt, which I copied and named 2.txt).
Last edited by Ryan Murtagh; 05-21-2010 at 02:14 AM. Reason: Replaced text file attachments
Thanks,
Ryan
A word to the wise is infuriating. - Hunter S Thompson
Ok, I felt bad, so I added some validation to handle instances where the number of files selected exceeds the number of worksheets in the workbook.
Your code for importing the text files and applying the text to columns function has Macro Recorder written all over it. I don't have much experience programmatically importing text files, so I left everything after your "import the file" comment alone.
Please Login or Register to view this content.
Ryan, no need to feel bad. You gave me what I was asking for. Pike, I have to digest your code first. It looks appealing.
Below is what I needed!
Please Login or Register to view this content.
And below the working whole sub.
Please Login or Register to view this content.
your a bright spark and picking up coding very easy
always good to see a thinker that has a go
and updates the threads to solve thanks for posting your solution I'm sure it will help others
Ryan wrote:
Your code for importing the text files and applying the text to columns function has Macro Recorder written all over it.
For that reason I would like to give pike's code a try.
both of you, thanks for your help and encouragement.
Very happy to help. Thank you for posting your completed procedure, and good luck with your project!
to something likePlease Login or Register to view this content.
Please Login or Register to view this content.
people may notice that I use 2 personal buttons in the Home menubar and wonder how that is done.
2 files are involved. These files are burried in the xlsm file. To get to them you need to rename the xlsm file to a zip file. Then you can look into the package content of the xlsm file using the Windows File browser (use column view).
From my example.xlsm file copy the .rels file and paste it into your xlsm file. Then copy my customUI.xml file onto your desktop. Edit this file as needed and paste it over the one in your xlsm file.
Finally you need to write Subs to link the menubar buttons with your macros. For that see example.xlsm Module3.
Last edited by dschmitt; 05-20-2010 at 10:47 PM.
pike, your code does not import the quoted text file properly. What should go into one column is spread over several columns. You can check the results of your code with the files attached. I simplified the quoted text files to make the error more easily visible.
Please Login or Register to view this content.
Last edited by dschmitt; 05-21-2010 at 12:45 AM.
Hi dschmitt
the first code split the file lines up with the "," delimiter
this will just copy the whole line and place it in one column
Please Login or Register to view this content.
pike, putting the whole content in column A is not a solution.
Information for e.g. author, title, abstract need to be put into individual columns.
The problem is that your code does not distingish between the comma delimitors and the commas in the text. For that reason, when ever there is a comma e.g. in the abstract the text is broken up and separated into new columns. So, you end up with the abstract spread over x number of columns. x being the number of commas in the abstract plus 1.
The code obtained from the macro recorder does distingish and imports properly.
Now, I don't want to use up your time. In general the problem is solved. However, if it bugs you that your code does not do the job yet, I would appreciate if you would work on it. Your code certainly looks better than the macro recorder code.
Hi dschmitt
how were the txt file created?
the quoted text files were created by a commercial database.
What your code needs to do is ignore the commas within two " marks.
For example:
"abc","def","g,hi","jkl"
Your code needs to ignore the comma between g and h.
ok... opened the txt file
andPlease Login or Register to view this content.
Last edited by pike; 05-21-2010 at 05:12 AM. Reason: use x as i in use
the only problem left now is that in each cell the content is bracketed with " marks.
For example
A
1 "Title"
2 "This gene..."
back cooking dinner
arh
what the code does is looks at every letter to build up the textPlease Login or Register to view this content.
once done it will strip off the first and last "
pike, I found a new error. This one is most likely the last one.
In the case of one blanc cell the code does fine
e.g.
"514,"","2010"
however, sometimes towards the end of the columns several cells are blanc.
In that case the macro crashes.
e.g.
"514","2010","","","","","","","","","",
I attached a sample data file.
I feel bad that again I let you do this problem. It sounds like not to difficult to solve. But yet again I have no time to work on it.
I found the time to have another look at the remaining problem. Below is the solution.
And below the whole sub. I attached a sample quoted text file.Please Login or Register to view this content.
Please Login or Register to view this content.
Hey dschmitt,
well done, thanks for posting back with the solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks