Problem with Macro to extract data from Source to Output file

[elgato74]

Hello


please have a look at the attached two files. One is the source file with order data and the other is the output file.

This is the code of the Macro so far:

Please Login or Register  to view this content.

However, there is a problem. If you look at the source file, there may be customers who ordered more than one item. If that is the case ( in my file Yvonne Hell ) the source file has additional rows for the items.

When I run the Macro, it leaves the concerning filed empty, unfortunately.

Can anyone help ?

p.s. both files must not be in the same directory, or else the macro will not run

[Marc L]

Hi !

From Windows Explorer, double click on source text csv file : some columns are yet empty ‼

[elgato74]

Hi there

not sure what you mean.

SalesHistory.csv doesnt have empty columns...

[Marc L]

Just proceed like I wrote and of course you must see some rows have empty columns ‼

And where in your code you open that text file ?

[elgato74]

Look at the code: you see the columns that are extracted, not all are. The problem is in Column M of the Source file.

[Marc L]

As I don't see where in the code the source text file is opened …

And some rows in this text file have column M empty, in particular for Yvonne Hell ‼

[elgato74]

Ok, I made it easier to understand. See the 2 new attachments. Download the Source.xls to your desktop ( you might have to change the path ) and the Outfile file with the macro to some other location. Then run the only macro.

[Marc L]

Now I've an important meeting, I have to go to the swimming pool !

It will be easier to understand with a crystal clear explanation with an attachment of expected result,
with that I'm sure you will have more answers …

[elgato74]

Enjoy. Now I made it crystal clear for anyone

[elgato74]

As I don't see where in the code the source text file is opened …

And some rows in this text file have column M empty, in particular for Yvonne Hell ‼

That's how Ebay gives us the data. If buyer buy more than one item, first cell is empty and underneath come the item names. Macro shall consolidate those type of entries, e.g. Yvonne Hell bought the three items ...

[xladept]

I don't know about crystal clear but here's what I've done to get some result:

Please Login or Register  to view this content.

[elgato74]

... it gives me a "Compile error - syntax error" and marks "*FPath = Sht.Parent.Path"

[xladept]

That's just so that I could run it without setting up a special folder - comment out my code and uncomment yours for both those
"F" names

BTW - did you add the asterisk?

[elgato74]

I got it to work.

However, i need the empty cell in Column F to be removed ( where the red X is ) and the customer name copied down in Column B.

Like here:

https://www.dropbox.com/s/qlj4ooic4b...e0003.jpg?dl=0

[xladept]

Hi elgato,

I don't get those problems - perhaps on your input csv?

[elgato74]

Hello !

try yourself, you will find an empty cell in Column F in the Output file. Yes, the Input has an empty cell too, but the Macro should get rid of that, that's the mission Column F may not have any empty cells ....

[elgato74]

... it should look like the attached file when the Macro is finished !

[xladept]

Maybe:

Please Login or Register  to view this content.

[elgato74]

Pardon my stupidity where do i put this code ? Or is this an additional macro ?

May I ask you to post the complete Macro Code ?

[elgato74]

i inserted

Please Login or Register  to view this content.

but then it gives me an error

"For control variable already in use" and marks "For i = 2 To"

[xladept]

Oops! my bad

Please Login or Register  to view this content.

[elgato74]

unfortunately, another error. Now "Next without For"

[xladept]

Oops! my bad Again

Please Login or Register  to view this content.

[elgato74]

Nope, that does not work either. I will be posting both files again, the Source File and the Output File with the Macro

[elgato74]

see files attached

[xladept]

Well, I fixed that patch but still don't match your expected output values

Please Login or Register  to view this content.

[elgato74]

strange

[xladept]

We're probably not loading the csv file correctly!

It's not a strong point for me.

I'll present this to the Forum

[elgato74]

OK ! Thank you very very very much

[Marc L]

From source text csv file and according to your post #17 attachment,
at beginner level using the old via romana way :

Sub Demo() 
           Dim CSV$, R&, SPQ$(), VA$(4), S$(), L& 
               CSV$ = ThisWorkbook.Path & "\Source.csv" 
        If Dir(CSV) = "" Then Beep: Exit Sub 
        Sheet2.UsedRange.Offset(1).ClearContents 
        R = FreeFile 
        Open CSV For Input As #R 
        SPQ = Split(Replace(Input(LOF(R), #R), """", ""), vbCrLf) 
        Close #R 
        L = 1 
    For R = 3 To UBound(SPQ) - 3 
            S = Split(SPQ(R), ";") 
         If S(2) > "" Then VA(0) = S(2): VA(2) = S(4) 
        If S(12) > "" Then 
                L = L + 1 
            VA(4) = S(12) 
            Sheet2.Cells(L, 2).Resize(, 5).Value = VA 
        End If 
    Next 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[elgato74]

Hello Marc,

I like your solution, it is fast and easy, however: it does not add the comments. The comments contain the name, the street name & number, the Zip Code & City, Country and Tel Number. See my code in the very first post.

Can you add this to your code ?

Many thanks ! Felix

[Marc L]

You can mod my code with your code …


My code follows exactly your expected result from post #17 :

... it should look like the attached file when the Macro is finished !

In this attachment there are only 3 filled columns ‼


This thread has now a third page but if in your initial post you had joined a source file and
an exact expected result workbook (already just with data without running any "code")
you may have a quality solution from the first answer !

[elgato74]

I tried, but i am not able to be successful

Would you mind ?

[elgato74]

i think the concerning code where the comments are created is this

Please Login or Register  to view this content.

Can you put this in your demo-code please ?

[Marc L]

As required several times, just attach a source csv file and an exact expected result according to this source file !
If at least a true explanation was in the initial post explaining the output layout …

Should we guess ? Of course not ‼
No effort on your side ? Why would we do some then ?!

As answers are at level of initial presentation, the better initial post, the better and quick solution …

[elgato74]

Thanks Marc for patronizing me. You take care of yourself.

[Marc L]

Within my code at beginner level, during execution in step-by-step mode or via a break-point
see within Locals window in S variable in which index (column) data are …

[xladept]

I've been playing with it but I think the comments still need tweaking

Please Login or Register  to view this content.

[elgato74]

... nearly there. But the comments are not filled correctly. The first 4 are correct, but then they get mixed up.

[xladept]

Well I reopened the source book and used your old code - perhaps we could translate into Marc L's notation??

[elgato74]

not really. but anyway ... too much frustration for all parties I suppose. Thank you muchly indeed

[Marc L]

As required several times, just attach a source csv file and
an exact expected result according to this source file !

Is it so difficult ? At schoolchildren level !

[elgato74]

no need to get personal Marc and insulting

[elgato74]

Source file and desired Output attached.

Thanks all

[Marc L]

For this result my code is enough 'cause there are only 3 columns …

[elgato74]

I am sure you noticed all the comments. Those need to be filled with Data from the Source file

[xladept]

Still getting the comment glitch.

Please Login or Register  to view this content.

[elgato74]

... time to close the thread unsolved. But thank you so much for all your time and effort ! Cheers

[xladept]

Unsolved and unforgotten

[Marc L]

(deleted …)

[Marc L]

alerte2.gif I do not know why but within your workbook the markers of inside cells comments were not visible !
As I can't see those markers, I can not understand as it is very not difficult to explain
you do not expect visible texts in a Comments column but hidden within cells !

Mod is at beginner level, just needing to warm a couple of brain neurons !
I can't see a glitch but tell me in case of any :

Sub Demo4Noobs() 
           Dim C$, R&, SPQ$(), L&, S$(), VA$(4) 
               C$ = ThisWorkbook.Path & "\Source.csv" 
        If Dir(C) = "" Then Beep: Exit Sub 
        With Sheet2.UsedRange.Offset(1):  .ClearComments:  .ClearContents:  End With 
        R = FreeFile 
        Open C For Input As #R 
        SPQ = Split(Replace(Input(LOF(R), #R), """", ""), vbCrLf) 
        Close #R 
        L = 1 
    For R = 3 To UBound(SPQ) - 3 
            S = Split(SPQ(R), ";") 
         If S(2) > "" Then 
            VA(0) = S(2) 
            VA(2) = S(4) 
                C = Join(Array(S(2), S(5), S(9) & " " & S(7), S(10), "Tel. " & S(3)), vbLf) 
         End If 
         If S(12) > "" Then 
                    L = L + 1 
                VA(4) = S(12) 
            With Sheet2.Cells(L, 2) 
                .Resize(, 5).Value = VA 
                .AddComment 
                .Comment.Text C 
            End With 
         End If 
    Next 
End Sub  

fleche.gif Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[elgato74]

Never mind Marc, you do not even understand the task. Go back to your swimming pool. Fare well. Thread closed.

[xladept]

Thanks for the rep!