Import more data into spreadsheet already worked on

I haven't checked your attachments yet...is the spreadsheet already being opened (in which case you can just grab the value of col. P)?
If so,t hen transferring it to your new col.T is simply a matter of putting the value in with a leading apostrophe, and checking for a blank would be simple enough too:

...

strCreditCardNumber = extractedWorkBook.Sheets(something).Cells(iRow,16).Value
If strCreditCardNumber = "" Then
   strCreditCardNumber = "not assigned yet"
End If
cells(iRow,20).Value = "'" & strCreditCardNumber

Select allOpen in new window

...

does that help?

1) Will the file be opened when you want to perform this update or closed ? Like would you expect this routine to automatically kick-in when the hyperlinks are being updated for each record ? or you want to click on hte file then activate a macro that will update the CC ?

2) Checked both horzontal and vertical version can we assume that in both cases the sequence is the same ie CC will be the last item on the list ?

3) Last but not least case you have multiple .xls files as you already mentioned previously is there a fix pattern we can catch to know that this file is the one containing the CC ?? like a header of some sort etc... that will always be at the same location .. ??

4) Could it happens so that you may have several records to update in the same file ? If yes then how to attribute which record corespond to which row ?

gowflow

gowflow:

1. Applicant Status is always open. The saved spreadsheet (which is called the Profile spreadsheet) does not need to open.
Yes, I'd like to extract the card number when the profile spreadsheet link is inserted into Applicant Status.xls.

2. No, not always. Sometimes there is a number after it like 100 or something small for the horizontal layout. For the vertical there is no card number row/field, so there will not be a card number to extract from the vertical formatted spreadsheet. For the horizontal formatted sheet the card number will always be in Col P

3. The card number will always be in Col P so if there is a spreadsheet that doesn't have a 16 digit value (it could have spaces or dashes as well: 5380-0136-0192-2341 or 5380 0136 0192 2341)

4. Please keep in mind that a single spreadsheet could have multiple records - one row per record - which is something we may not have talked about when you created the 'Save Links' function. We then should extract both the 'from' email address in the email and the email address input in the profile sheet (in Col O for the horizontal or Row 16 for the vertical)   to Col D. Matter of fact we should do that anyway because they are often different.

Also, the vertical profile sheet will never have multiple records. It will always only have one column. Wow! a lot of variables here to contend with.

Does this answer some/all questions or create new ones?

all is fine EXCEPT
item 4) should be treated separatly in a new question as altogether  diffrent scope. Purpose of this question is to update CC so sake good order if we find multiple rows will only treat the first one.

So let me summarize if we find a vertical then automatically we should conclude No CC available so in fact we should only treat any file that have value in Col P or else it is a no CC available right ?

gowflow

ok with your exception. will post another question when through with this one

You are correct in your summation. Yes. But let's say: "Not Assigned Yet"

Not Assigned Yet
sure you already mentioned that but was too lazy to look backward for the exact sentence !!! :)
will revert.

Happy labor day !
gowflow

any updates on this, gowflow?

Sorry was off for easter holidays will attend soonest tks.
gowflow

Easter? Different days for us. We had ours last month. Hope you had a relaxing or rejuvenating holiday.

I am orthodox and its now last month's was catholics some years its together and unfortunately some others its appaert hope one day mankind will get united and have 1 common calendar !!! :)
gowflow

Hi gowflow, Running the function this morning I got the attached error.

and what did the error say ?
gowflow

attached

Most likely the LastRow variable is less than 1.
You can check by clicking Debug then hover your mouse over the word LastRow and see what shows in the tooltip.

Of course, the key is to determine why this happened, and most likely it was the check in the Loop Until line, which caused it to not find what you wanted (which looks like a white background).  Did you happen to highlight the row in a different color (or maybe NO color since I think that shows up as something like -4097 even if it looks white.)

ok in the sub SaveAttachements please replace this line
Loop Until WS.Range("A" & LastRow).Interior.Color <> 16777215

by this line
Loop Until WS.Range("A" & LastRow).Interior.Color <> 16777215 Or LastRow = 1

save and exit the workbook and try again. Will revert on the main issue of this question.
gowflow

thanks gowflow. we're back in business. that seemed to work on two that I processed today.

Thank you!!

sorry for the delay will look at your issue in the next few hours and revert.
tks/gowflow

any updates here, gowflow?

gowflow, you ok?

Jase, where do you stand at this point?  What do you need help with?  It seems that you got the error resolved.  What's left?
--
Rob

the error was resolved, yes, now hoping and waiting for the solution request originally posted before this error came up. I think gowflow is on it, but haven't heard from him lately.

sorry again will attend at once.
gowflow

2 more questions as not obvious:

1) You mentioned the file name is called 'Profile spreadsheet' will it be always this name or the name can change ? like is it 'Profile Spreadsheet.xls' or 'Profile Spreadsheet.xlsx' or ... what would be the exact puctuation of the full name ? or is it always a diffrent name ?

2) you mention that the Card number will always be in Col P then can we assume the following ?
Than P1 will always have a header called 'Card Number' and the value will be in P2, P3 ... ??? or you could also have values of credit card in P1 with no header ? for sure we are talking here of the horizontal format as in the vertical Col P is blank anyway and will trigger
'Not Assigned Yet'

gowflow

1. no, it is never named that. It comes in all sorts of names. and it could be .xlsx or .xls or...?

2. yes, col P (hopefully - it's supposed to) for the horizontal layout. And the heading should be Card Number and the values will be as you said in rows below Col P.

yes, horizontal layout only and vertical: not assigned yet

and just to make sure, there could be multiple rows (for the horizontal layout only) with a different record and card number for each row

ok for all previous comments.

and just to make sure, there could be multiple rows (for the horizontal layout only) with a different record and card number for each row

ok how to deal with multiple rows then ??? will talk abouut it as you already mentioned it and mentioning it now again. Do you have a pattern ? like what to update and where ??? as remember we are clicking on a specific row and it is downloading email attachements links and we are updating 1 row with email address and date and the rest of columns with attachments links now you want to update a CC that is fine as long as we are still in the 1 to 1 scenario. Now you say there may be more than 1 row in the attachment ie more than 1 CC in that attachement then how do we update all this ?

I had suggested we update this question basis 1 to 1 and then later in a diffrent question then you can expand the 1 to 1 to be 1 to many basis on clear criterias.

Appreciate your views to see how to go from here.
gowflow

the 'more than one row' (for horizontal only) will always be another record to insert in Applicant Status. Can almost treat it as another spreadsheet but in the same email

I agree. let's deal with it later as there will be other functions to apply to this sheet going forward.

thank you.

can you please give me sample email that have these horizontal lines as the one you attached in the previous question had a vertical one. This would make my life easier as need to trap something on an email while reading the files ... If more than 1 then its better and if also one with several rows so much the better for future.

tks/gowflow

Attached and as you can see for some reason sometimes I get duplicatednew-signups.msg attachments

not sure if that worked so attached is the spreadsheetCopy-of-1-14-12-Spreadsheet--4-.xlsx

thank you! installed and now just waiting to try it out with next applying email. will let you know

Wonderful! Thank you, gowflow.

Added functionality to this spreadsheet coming up and will post link here, if you're willing.

Thank you and pls let me know anytime.
gowflow

Thanks, gowflow and here it is:

https://www.experts-exchange.com/questions/28133253/Part-3-to-Import-more-data-into-spreadsheet.html

Very much appreciated. Thank you.