Add date file via vba

why is your post written in such big letters? I don't think some will like that. but at any rate....

excel CAN import XML data. it is in the interface menu, if you care to look. in terms of selecting a file to get data from, you will need to take a look at this coding concept:

filedialog
https://docs.microsoft.com/en-us/off...ion.filedialog

in terms of getting data OUT of a file by way of a data stream (which is called I/O), you need to take a look at these:

openTextFile
https://docs.microsoft.com/en-us/off...extfile-method

readline
https://docs.microsoft.com/en-us/off...eadline-method

input statement
https://docs.microsoft.com/en-us/off...inputstatement

thanks for reply, I know about import xml in excel but that not help me

thanks for ur links but I need ready vba code if there, I am not pro with writing vba code

Thanks

upload your file, and specify (give an example of) what information you want to get out of the XML file and where you want to put it in the worksheet. someone will surely help get it started.

I upload excel file with xml file

The excel has original Table sheet and settings table sheet
I want if I click import, system allow me to select xml file
then macro read the file I choose, and see my setting table
and paste only values of all equals data as I wrote in settings table, in the original table
paste only values without changing anything or format

I mention two heading in settings sheet, after I get the macro code I will add my others settings, it will be same idea.. its just defend xml schemes to heads
kindly I don't need import by excel options, I want this custom

* XML file uploaded as txt, forum didn't allow to upload xml, kindly convert it

Thanks

well, now I (or others) can probably help you. however, what you have said is still unclear. these words by you:

and see my setting table
and paste only values of all equals data as I wrote in settings table, in the original table

all equals data? what does that mean? it sounds like you want to COPY data that is in your original table, TO the settings table. is that what you want? because there is NO data in your original table. if you only want to copy specific XML data out of the XML file and into the settings table, you need to specify what that is. more than likely, if you can give this info, I or someone else will get you started. but personally, I'm not going to do it all for you. I'll give you a pointer to start.

by the way, the data in your XML is NOT Xml-formatted data.

I want copy data from xml file.. to orginal table.. not all xml schemes.. only schemes that i select it in settings table to mentioned heads

well now.....thanks much. THAT was clear and precise! good deal. so, more than likely this will get you there. put this behind your SETTINGS table:

Please Login or Register  to view this content.

now, you do the rest.

https://docs.microsoft.com/en-us/off...eadline-method

https://docs.microsoft.com/en-us/off...tstream-object

https://docs.microsoft.com/en-us/off...extfile-method

Thank you!.. I will test it tomorrow but as I see in code.. u want to upload specefic xml file.. "c:\xml.txt".. this just a sample.. i want user brower hes Desktop and select the xml file

and what do u mean by I do the rest? Guid me..
Thanks again

Hey again and thank you again, but I really I dont know what ur code is doing.. its not what i am looking for>> sorry for that

Maybe I didnt tell clear what exactly I want, soryy my English not very well
anyway I make video to show you what I want exactly

hope its clear
Thanks for your help
update excel uploaded
https://drive.google.com/file/d/1pES...ew?usp=sharing

alright, well I guess you do not have much coding experience. you didn't have to show me the video. I knew what you wanted from the beginning. so, since I'm feeling generous today, I will do it for you. but, I would hope you could take what I'm going to do and learn from it. cuz i'm not going to do it again but of course, you can always ask questions here like everyone can. I simply wanted to encourage you to learn how to do it on your own. I do that with everyone. give me an hour or so....

Thank you for ur words, I really benefited from the forums

Please Login or Register  to view this content.

make sure you change the following lines of code and take the spaces out of the lines of text. this forum's security software blocked the actual code because it detected it as HTML code. no surprise there!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

now, you can do what you wish with that. good luck. and you're welcome.

Originally Posted by e97h

WOW! Thanks you!!
Fantastic work .. Thats exactly what I want

Now if I want add new rules, I just need to add rule in rules table + add this lines?

Please Login or Register  to view this content.

the following lines of code:

Please Login or Register  to view this content.

refer to the column NUMBER that description and price are in, in the sheet table. the following line of code:

Please Login or Register  to view this content.

is assigned which ever number of column is relevant at the given time the XML line is being read.

Originally Posted by e97h

One more point, in the xml file I uploaded, there 4 values for PRICE, and 4 for Description
I don't know why excel didn't detect first PRICE, and detect only 3 value from 4.. would u check?

the code I wrote checks the FIRST XML line where the code < item_price > after any given line where < description > is found. and because of that, only one price for each description is put in the sheet called table. if you want to put ALL of the prices that appear after any given line where < description > is found, the code will need to be changed.

yea there only one price for each description, but I mean excel skip the first < item_price >
item_price came before Description it the xml file, I try to replace item_price and make it in IF condition, and move description to elseif, but same, first item price skippd

are you referring to the data in the image? and if so, are you asking me to change the code whereby it skips the first item price and good description? if not, what do you want?

I mean macro already skipped first price only.. It must not skip it

thats why in my image there is 3 prices with 4 descriptions.. first price skiped.. I dont want to skip

Really Thank You for your help
but macro still didn't read all xml tags successfully, its skip some tags, example in this xml file I have 12 tags in rule, its print only 9 tags, and avoid 3 tags

PHOTO : "ERROR" mean macro didn't read the line
I remove unused tags from xml file to be more clear

really sorry to keep asking, I try to solve it but I cant handle it

* convert txt to xml, I cant upload xml in fourms
Thanks

Originally Posted by Haluk

your XML files are not properly arranged.

this is absolutely correct. XML data is always arranged in a hierarchial manner, and yours is not. it is scattered. that's why my code doesn't make a lot of sense, but it really should. It simply does a job, but not necessarily the right job if given clean data.

Sorry for that, very good code I understand your point
Last point and I am embarrassed to ask you again
can you link the codes with rule table? so I can add and edit rules from table only, so I dont need to edit vba code every time?
other way tell me what shall I add in vba code every time I want to add more rules, but if you can do it in the table will be very easier
And thank you again, SOLVED BY VBA_PHP

Originally Posted by e97h

can you link the codes with rule table? so I can add and edit rules from table only, so I dont need to edit vba code every time?

yes. keep adding rows of data to your ""reference table"". the one that has the XML tags in it now. then, write in the code a loop that goes from the top row to the bottom and point to the cells you want to capture within each line. like this:

Please Login or Register  to view this content.

but I cant add the code to main code, its hard to me
would u place it to the file?
and put same cell range that I have now, then I can edit the range

I think we're getting to the point where you owe me a cookie for this. LOL. or maybe a $10 dollar USA bill? fine. here:

Please Login or Register  to view this content.

oh hey, I just noticed something. I forgot to change one line to be congruent with what else I changed. this:

Please Login or Register  to view this content.

should be this:

Please Login or Register  to view this content.

sorry about that!

Now not working fine
I didnt see ur code in last update

Please Login or Register  to view this content.

10 items should print with this file
anyway u deserve the cookies, give me ur payapl

am I to understand that you need more assistance to finish this up? is that what you're asking?

if you can, coz still seems not working like I want

@e97h;

As an alternative, you can try the attached workbook.

e97,

try what Haluk posted.

@Haluk

Thank you!
Code working fine, its only didn't detect numbers if start with 0

Untitled.png
I can hide the error window, but it think its coz zeros problem
in the photo,
HS CODE should be 04....
- and 1 Shoud be 3 zeros
2 and 3 should be 4 zeros

I try to set format as text but same

Seems, a Schema file should be used also with the XML file..... i don't have much time now.

yea its xml.. anyway thank you good job

But, you can apply a custom format to cell A10 as shown in the picture below and then try the code again to see...

this code only allow me to see the value like I want, but actually its not the value
when I copy it and paste value only, zeros gone also when export the xml, zeros not there

ur code totaly fine, but why this zeros missing?

Hey, What you edit in excel rather than add schema in xml file?
I see its working for old xml files without I mention scheme in xml file

Because; when you import the XML file associated with a Schema file into the Excel file, the schema structure is integrated into the Excel file automatically and can be used with other XML files having the same nodes and properties.

This goes on until you delete or change the Schema in the Excel file.

mean the mapped xml file.. has that line for using the scheme?

Yes, it is the following line where the schema is declared as an attribute to the root element of the XML structure.

Please Login or Register  to view this content.

Thank you for helping, solved