Posts by homegrownandy

    Re: Print sheets from all .XLSX documents in a folder and its sub folders.


    I'm assuming its this

    Code
    strfile = Split(TFILES("C:\Users\Admin\Documents", True, "*.XLSX"), vbCrLf) 
        For lngLoop = 0 To UBound(strfile) 
             
             '// TFiles seems to return an additional blank element at the end.
            If strfile(lngLoop) <> vbNullString Then


    because lngLoop was a value used in the other macro.. I'm not so sure what I need in this one.


    I have two working macros. One which formats the printing as I want and one that retrieves the files in its folder and sub folders.


    In the code above I've attempted to butcher the two together with limited success.


    This will print the first document as I want but it then stops. It doesn't look for the next file.


    Any help appreciated.


    andy,

    Re: pagesetup and papersize macro.


    ahh I see the problem. Page setup was only being applied to orientation.



    That's the working code if anyone finds this in the future.


    This is what I currently have. But I'm getting runtime error 438


    object doesn't support this property.



    I'm trying to achieve the following.


    sheet 1 needs to be portrait a4
    sheet 2 needs to be landscape a3
    and IF sheet 3 exists it needs to be landscape A4.


    In this example sometimes sheet 3 doesn't exist. Would the macro just skip past it? or do I need to put IF statements in?


    Ultimately I would like to point a macro at a folder and it then proceed to print the sheets as described.


    Thanks in advance! Andy.

    wb.ActiveSheet.Range("C4").Value
    I don't want this to copy from the active sheet. I want it to copy from a sheet named "secondsheet" for the purpose of testing.
    However, when I change this, my macro will open the second sheet but then it will stop rather than complete the rest of the code.
    I'm confused as to why this is and would appreciate help.
    Thanks! andy.
    wb.secondsheet.Range("C4").Value <--- this is what I changed it to .

    Re: Pulling data for a mail merge


    Thanks for the reply and the great detail you have gone into to help me.


    For testing purposes I have created a directory called test in my documents.


    within that folder there is a file called test1.xlsx


    and three "new folders" numbered incrementally. Each of the new folders contains a xlsx document with test data on sheet one.


    I run the macro and it appears to run but I get no data back.


    strfile = Split(TFILES("C:\Users\Admin\Documents\test", True, "*.XLSX"), vbCrLf)


    Its close to working im sure, but I cant get any results at present. I've tried tweaking many things but nothing seems to work.


    as always.. Greatly appreciate your time and help and I hope we can over come this issue!


    Thanks! andy,

    Re: Pulling data for a mail merge


    I'm getting an error message as the script runs:


    #This workbook contains links to one or more external sources that could be unsafe.
    If you trust the links, Update them to get the latest data. Otherwise, you can keep on working on the data you have.#


    I'm assuming that links within the workbook the macro is attempting to open are dead, but why is this an issue? Its not pulling information from any of these links.


    I could be totally wrong but I'm unsure as to why I'm getting this message.


    Also.. How do I see which file is causing the issue? So I can go into the document and try to see the issue myself... At the moment its just running through and displaying this issue occasionally but I'm unable to see where it currently is in the loop.

    Re: Pulling data for a mail merge


    Quote from pike;748775

    Hi homegrownandy,
    If you done not get a reply its because its hard to understand what the question is
    Hard to say probably yes
    First record a macro of what you are doing


    Yea its not really one question, I was just basically saying what I've done and hoping to see where you guys could help me improve or tell me where I'm going wrong. I am using a few macros during the process but I am a novice.
    CYTOP:
    I will have to spend some time looking at what you have posted and see how that goes.
    Thanks for the replies guys, Ill post any progress (or most likely problems) in due course.
    Once again, much appreciated!
    Andy.

    I would like some feedback on my current process and hopefully some advice on how to improve.


    What I have is hundreds of excel reports all containing specific data in the same fields.


    For example: A1 = name, B2 = Postcode


    All of the reports are in individual folders.


    In explorer I copy file path of all the folders and paste them into excel all at once.


    I then open the hyperlink in the excel document to each of these folders manually and copy over the path of the document inside. This is pasted next to the file path for its folder. This is so I have a full list of all the needed documents within the excel file and I methodically work through them.Once this list is obtained I use the following code to pull the data from the relevant place:

    Code
    =INDIRECT("'["&$D8&".xlsx]"&$E8&"'!"&F$2)


    This all works perfectly but it means for the code to work I have to manually open each document, I then run a macro which will move the row onto the next sheet and paste it as values (so the data doesn't disappear when I close the page.)



    Due to my lack of knowledge in excel and in VBA I'm sure there are ways I can improve what I'm currently doing. I have highlighted in bold two areas I would like to improve efficiency but I'm also willing to restructure the whole project.


    The data is going to be used for mail merge and that aspect of the project is all set up.


    I would appreciate anyone's input.


    Thanks! Andy,