Please Help. Got Logic but dont quite understand how to Code it . Thanks :)

[simplificated]

Here what im trying to do (using excel vba)

Please Login or Register  to view this content.

/*all my work books are labeled WCSO#1(2013), WCSO#2(2013), WCSO#3,2013.....AND SO ON. (all in consecutive order) */

1. i want to search for a closed workbook
here's how the logic should perform the search:
(a) look at the current workbook and "get the name of the current workbook".
(b) using the "mid string function" MID( text, start_position, number_of_characters )
search the string and retrieve indexes in the 6th, 7th and 8th position "only if they are numbers and follow each other"
(and because do not want the numbers in the bracket to be counted in i.e.(2013).
the numbers in WCSO sheet begin at index 6.

(c) after the numbers are retrieved, place them in a string variable (for ex numString) then Convert them to an integer (numInt = CInt(numString))
then add one to the integer numInt (i.e. numInt = numInt +1)
then convert numInt back to a string (i.e. numStringNew= CStr(numInt) )

(d) Use the replace function (Replace ( string1, find, replacement ) to find and replace the previous string in the current workbook name.
I will copy the current workbook original string to a different variable.
(e) now place that string variable in (what ever formula you use to reference a closed workbook and its sheet and cells withi)
im not sure if this is ok but heres what ive got :

Please Login or Register  to view this content.

'i don't know how to open reference the sheet or the cells in range in the sheet
' i need to get the data (let says in cell A1:A50) and place it in my current workbook in the same range (i.e. Cells A1:A50)

Please Login or Register  to view this content.

[alansidman]

Welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them for you this time. Please read all our rules and please abide by them in the future.)

[mikerickson]

You might want to use this UDF. NextIndex("WCSO#1(2013)", "#") returns "WCSO#2(2013)"

Please Login or Register  to view this content.

[simplificated]

ok heres what ive done: Almost completed
pleas look at the code that has "Problem Here" written in it.

it work fine once I explicitly state the name of the file that is located but I don't want to do that: I need to place the variable closedWorkbook in this spot

Please Login or Register  to view this content.

how can I fix this? ( I don't want to point to a specific file ...because I wont know what the file(workbook) is until it has been determined by the loop below.
thanks.

Please Login or Register  to view this content.

[mikerickson]

Rather than looping to get loopPosition, you could use

Please Login or Register  to view this content.