1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

question on macro that analyzes and combines files considering only the files with tables.

Discussion in 'VBA Macros' started by stefanoste78, Jul 5, 2018.

  1. stefanoste78

    stefanoste78 Member

    Messages:
    126
    Good morning.
    I would like to ask you a question based on your experiences. I will list a series of points in such a way that you can tell me which of these can be done.

    Starting situation: In a folder I have files of different types (Excel, PDF etc. - if it is not possible with different files I could transform pdf files into Excel files, just as I could divide the different files into different folders by type).

    Could you tell me which of these points you can do:

    1) analyze each file to understand which of these contains a table (we could analyze the structure of the file invading the existence of different columns within them) by deleting files that do not have tables;

    2) count how many times a string is repeated within each file containing tables;

    3) Combine the different files containing tables. Merge should be done by adding, to the file to be created, the different headers contained in each file (if already exists there is no need to insert the header in the file to be created) and move the table contents in the new table according to the header.
    Example:
    Table "1" has the following column headers: "a, b, d"
    the table "2" the headings: a, c (the heading "a" is the same for both tables)
    from the union of these two tables a new file will be created with only one table inside that has the column headers equal to the two tables: "a, b, c, d" and the contents of each table.

    Thank you
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,055
    Possible for all of above.

    1) will require PDF to be in Excel format. As well, table should be "Excel Table", not just data organized in table like structure.

    2) Various methods available. But method that should be utilized will depend on actual workbook structure. The most flexible one is Range.Find method.

    3) isn't easy without PowerQuery. PQ, by design, will add new column, if header does not exist in table that's being appended to. Will give null value for missing data (column) on either table. Otherwise, you'll need to handle missing and/or extra columns in your code/process.
  3. stefanoste78

    stefanoste78 Member

    Messages:
    126
    To transform the pdf use this site:
    https://www.pdftoexcel.com/

    in the case of text files, the transformation can happen with a macro, right?

    What is the difference between "Excel Table" and the data organized in a structure similar to a table?

    I read that powerquery is a component of excel. Can you use the free version or do you need the paid version? Is PQ used to create the macro or even to use it?

    In the end to create such a macro is complex?

    I think it would be a useful macro for many ..

    Thank you
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,055
    Your question is too broad to give specifics.

    When programming, I tend to avoid use of web services. Since, you never know when they'll change URL or even close.

    Read up on link below for Excel Table structure.
    https://support.office.com/en-us/ar...l-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

    PQ comes as standard feature in Excel 2016. Otherwise you'd need add-in. Add-in for 2010 isn't updated, and may lack features available in current version.

    PQ to some extent can be manipulated via VBA, but most process can be automated without use of VBA in PQ.

    Yes. Especially when data structure isn't well defined. Code must be written in such a way to handle all kinds of exceptions. Not a easy thing to do.

    Sure, but I believe in enforcing rules at data entry point rather than spending resources to clean messy data. So to that end, I often create data entry forms and transformation steps, that are specific to my reporting needs. Not the other way around.
  5. stefanoste78

    stefanoste78 Member

    Messages:
    126
    Hello. Thank you for your reply. In the text of the link you sent me earlier, it states that a table of excel is a combination of data organized by columns and rows (what it should be) then states that it should have other settings like filters etc. For me, a table is the first indication ...
    I have to try to download power queries (I can probably use it myself that I do not understand anything about macros).
    As for the question on the use of power query I probably will not have understood correctly (because I've never seen it and tried it), or, the translator did not translate in the right way. When using power query, can the macro be used by a user who has no power query?
    Thank you

    ps.
    The translation is not correct regarding the use of the transformation link. I wanted to write to you that I use this link for the transformation:
    https://www.pdftoexcel.com/
  6. stefanoste78

    stefanoste78 Member

    Messages:
    126
    I have done a search on google using as a search key the title of the file that I attach here.
    Probably among these links there is a macro that combines the files as I indicated in the post of origin. since I can not program, could you tell me which is the right link to consider?
    Thank you

    Attached Files:

  7. AliGW

    AliGW Active Member

    Messages:
    288
    Please provide cross-post links - I believe you've posted this on at least two other forums. ;)
  8. stefanoste78

    stefanoste78 Member

    Messages:
    126
  9. AliGW

    AliGW Active Member

    Messages:
    288
  10. stefanoste78

    stefanoste78 Member

    Messages:
    126
    It is necessary to copy and paste the entire link instead of clicking on it. I do not understand why the whole link has not been recognized as a hyperlink.
  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
  12. AliGW

    AliGW Active Member

    Messages:
    288
    Marc - you haven't said why you quoted my post. Are you taking issue with it or reinforcing it? Or am I the "idiot" in your signature line?
  13. Marc L

    Marc L Excel Ninja

    Messages:
    4,257

    Dear Ali,

    of course it's a reminder which must obviously be respected
    whatever his excuse
  14. AliGW

    AliGW Active Member

    Messages:
    288
    Thank you for clarifying. :)
  15. Marc L

    Marc L Excel Ninja

    Messages:
    4,257

    You're welcome !
    A reminder as per this forum rules …

    stephano, as the last warning, next time this thread will be closed …
  16. stefanoste78

    stefanoste78 Member

    Messages:
    126
    Hi Mark.
    If you like, you could also contribute to the post. It will surely be appreciated by many others.
    Is your strong power query?
  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    As you already know, I never contribute in case of wild cross posting.

    And as there are samples everywhere on the web
    and as copying data is at beginner level …​

    That's funny, me too !
  18. stefanoste78

    stefanoste78 Member

    Messages:
    126
    Google translations often change the meaning of sentences.
    I'm not able to program, unfortunately ...
    It is a skill to program ... as any art ..
    Chiro wrote in previous posts that it is quite difficult to create a similar query, maybe for you it is not ...
    As for the cross post, I wrote here the first one. On excelforum, Sandy666, is competent enough and willing to help me.
  19. AliGW

    AliGW Active Member

    Messages:
    288
    But there is another forum where you have asked the question, and you haven't posted the link to it here. Why don't you?
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,055
    Yes, I did write it is difficult, since you had wide open condition ;)

    At any rate, you got solution from sandy666. Always remember the advice you got, post clear and concise requirement/question, along with sample data and expected output.

    And the cross-posting rule.

    This will get you higher quality solution(s) faster.
  21. stefanoste78

    stefanoste78 Member

    Messages:
    126
    Thank you.
    It can be difficult to understand how to set a request when you do not know the different ways in which you can achieve the results.
  22. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,055
    Detail your manual process of combining file(s)/tables. Clearly demonstrate how you get from data to output/result. Note your Excel version. Then, we can deduce what tools you have access to (typically) and what methods may best suite your need.
  23. stefanoste78

    stefanoste78 Member

    Messages:
    126
    Thank you. You are precise ...
  24. Marc L

    Marc L Excel Ninja

    Messages:
    4,257
    Before creating any thread, at least think about your need
    and only once you understand it, clearly explain it
    as you were yet several times warned in your previous threads …

    Even if you do not know how to code
    - as children learn basics with few days, using the Macro Recorder -
    you must first analyze how to in an Excel way and share your logic
    in order we must know you understand your need !
    And on technical points not in a general view …

    So analyze your need and explain at least
    what is your strategy to find out a workbook has a "table".

    Of course after adding the missing expected link ! As per forum rules …

    No clever analyze, no code as few have time or any will
    to decypher your mind as here it's far not a mind reader forum
    neither a do-my-job-in-order-I-get-the-university-chair.com !
    As it's just about thinking, so working, sharing instead of just grabbing.

    « If you can’t explain it simply, you don’t understand it well enough … »
    Albert Einstein

Share This Page