Posts by Kenneth Hobson

    Running every 12 hours does not ensure that the email is sent. There are ways to do that if needed. Normally, I leave Outlook open so it is not an issue.

    I have upload an example to run a routine on open. It shows a popup option to allow you to abort the run on open if opened manually. Using this method, I put the workbook into a Windows Scheduled run.

    If you went with method 2:

    You have many issues with your code.

    First off, you would be well served if you used Option Explicit as first line of code in objects like userforms, modules, and such. This will force you to Dim the variables in each routine if not global. e.g. You set the worksheet object as ws1 in one routine but did not in the other routine but used it as if it existed. I set this line automatically when I add VBE objects in my VBE's Tools > Options > Require Variable Declaration.

    Another issue is your use of Column. Since you only have one column in the combobox, Column use is not helpful.

    You should decide how you want to do the lookup. (1) You can go by the value selected in the combobox and use formula lookups to get the column values, or (2) fully fill the combobox so that you can use the combobox's Column method. (2) would be my recommendation unless your lookup range is huge.

    Always code with structure. This means indent your code for the loops and such. This makes code easier to understand and modify later. I use two space character widths when I press Tab key for my indents so it may not be that obvious in pasted code here. The default in the options is 4 space character width tabs.

    Prior to a Run, Compile (Debug menu) your project. This may catch some syntax errors.

    Once you decide on the lookup method, post back and I will show you how to do it.

    Welcome to the forum!

    I don't know if B4 is in ws or the ActiveSheet.

    Rather than sheet codename for output, I used:

    If code could lower security, it would make a lot of crooks rich.

    Search for: "change security settings in yahoo mail"
    e.g. Same concept is done for security cameras....


    How to Allow Less Secure Apps to Access Your Yahoo Mail

    • Applies to: All Reolink NVRs and Cameras.
    • Log in to Yahoo mailbox and go to Account Info page.
    • Choose Account Security and turn on the button of Allow apps that use less secure sign in.

    Cursor position on the screen can change for a range. The usual method would be to determine the locations for window maximized in the Test sub. If window is not maximized, or zoomed, the coordinates will be wrong. Change coordinates to suit your window.

    In a Module:

    Right click the sheet tab, View Code, and paste:

    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. PositionXY
    3. 'IWCoords
    4. End Sub

    Welcome to the forum!

    As I explained, I do not have Acrobat so I can only parse the text file that I created with pdftotext.exe. You can download that and easily test what I posted. Just change the things in red and play from that worksheet active. The only thing that needs changing maybe is the description.

    Once you understand the concept of parsing, it is easy. Open the text file in Notepad, view the line number(s) where that data is and then use the concepts that I demonstrated. In some forms, I fear that it will not follow a consistent pattern. From the layout, that might just be the description "field". One can sometimes be more exact by searching for key words in the array elements rather than using line/array numbers. e.g. Line 5 in notepad would be "line"/row number 4 in the array since it is 0 based.

    Get it to "work" first, then one can set it up to batch process all as post #8 shows or one at a time using a file dialog pick method or such. It is not difficult to manually add the parts in red from post #11.

    As I explained too, if you do want to use Acrobat to get the text, I can show you how so ask if needed. I would need it to help you parse as I did in post #11 I suspect. Acrobat may parse the file more different than pdftotext.exe.

    This is ok for one file's data import but if many, I would remove the formula in column K and write the row all at once using an array to speed it up. There were a few columns that I was not sure about.

    Here is the pdftotext method with parsing. The Acrobat method would be similar but the raw data string would be split to array "a" directly. Since I don't have that data, I don't know if the raw data would be in the same locations as what pdftotext.exe does.

    Work is in the eye of the beholder I guess. Your pdf file is not generic, nor does it have fields, so it needs some steps to solve. Post #8 was just to get you to step 1.

    You have to modify post #8 code to suit. The concept that I showed was how to get the raw data and put it into a text file using Acrobat. This is just the first step to your solution. You can copy and paste the function ReadAcrobatDocument() into a Module, add the Acrobat reference as commented, and then make a Sub to call it and pass the input values. You then get a string back which is the raw data string. In the example code in #8, I wrote that string to a text file. That step is not really needed for your problem since you have Acrobat. It is useful if you want others to help you parse the string stored in a text file. The column A string that you showed was cut and pasted so it may not be consistent and not useful for automation anyway.

    I used your pdf file from post #1 in an online pdf to excel site as I said in #8. The converted Excel file would not be that useful to your step 2 needs. It could be done but paring would be more involved than what I detail later. As such, I am not sure how much help posting that code would be.

    Making a short Sub to call the function in #8 is easy. If you still need help with that post back.

    Step 2 is to parse the raw data string to get the data parts that you want. This will be the more tedious step. It should be doable but depends on the raw data string. From step 1, you can attach a text file with the string if you want help with parsing here in step 2 since others likely do not have Acrobat.

    So that others can do step 1 that do not have Acrobat, I used a 3rd party application from xpdf called pdftotext.exe. I have attached it so that you can see that parsing it may not be too difficult.

    Before I work on step 2 parsing, what worksheet did you want the parsed data to be put, SIRs?

    Before I start on step 3, it would useful to know if this is a one file at a time need or multiple pdf file data imports at once need.

    I will look at the online conversion of pdf to Excel since I don't have Acrobat on this computer. Since you have Acrobat, you do not need that. I have code that can do that in Acrobat.

    Without Acrobat, other methods are needed to get the raw text. For Acrobat, getting the raw text is easier than copy and paste. You should be able to modify this to suit for one file. This one gets the text content from many files.

    If you have Acrobat, not Adobe Reader, there are some methods one might use.

    Otherwise, I guess one would have to use a parsing method. The first step though would be to uncompress the pdf. For that, one 3rd party application could be pdftk. e.g.

    1. '
    2. Sub ken()
    3. 'pdftk doc.pdf output doc.unc.pdf uncompress
    4. ChDir "C:\Users\lenovo1\Dropbox (Personal)\_Excel\pdf\FDF\wp array method"
    5. Shell "pdftk ""UACSIR Sample.pdf"" output doc.unc.pdf uncompress", vbNormalFocus
    6. End Sub

    From there, copy and paste. To automate that, I would have to think on it some.

    Of course if this is not a standard need or standard format, I would not bother doing all that work.