0

Apologies if this question is elementary, i'm not great at VBA. I have 3 separate codes that i want to run one after the other at the click of a button. So i have the master code titled UpdateLinks that is supposed to call them. The first 2 (PreSelect & UpdateLinksCode)run the 3rd (PostSelect) doesn't. Individually they all work.

The purpose is to update links to external excel workbooks without having to manually enter the password. That is what the UpdateLinksCode macro is for. However, if the linked source does not require a password the UpdateLinksCode will drop the password in whatever cell was selected before running. Hence the PreSelect & PostSelect - supposed to delete the password and prevent exposure.

This is the master code:

Sub UpdateLinks()

    Call PreSelect
    Call UpDateLinksCode
    Call PostSelect

End Sub

The individual ones are as below:

Sub PreSelect()

    Sheets("Sheet1").Select
    Range("A1").Select

End Sub

Sub UpDateLinksCode()

    Const PWord As String = "password"
    Dim xlLinks
    Dim i As Integer
    xlLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(xlLinks) Then
        For i = 1 To UBound(xlLinks)
            SendKeys PWord & "{Enter}"
            ThisWorkbook.UpdateLink Name:=xlLinks(i)
        Next i
        End If

End Sub

Sub PostSelect()

    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.ClearContents

End Sub

It seems simple but i can't hack it.

Update: Appreciate all the input. I've now changed the code to sit in a module and refer to the worksheet by name. I've removed the call functions and separate codes and condensed it to the one. There are no errors when debugging or running through step by step.

However, same issue exists in that no further code will run following the 'send keys''. The "Range("A1").Select" needs to be in there because, something to do with the timing of the password request box popping up and the send keys inputting the password, causes half of the password to be typed into whichever cell is highlighted when the code is run. Therefore, i force it into cell A1 which has both background and text set to white.

Current code is as below:

Sub Update_links()
Worksheets("Staff Rota 2019").Unprotect "broncko"

Range("A1").Select

Dim PWord As String
PWord = "stevefinnan"
SendKeys PWord & "{Enter}"
ActiveWorkbook.UpdateLink Name:= _
"Y:\a - Staff Rota\Staff Rota 2019.xlsm", _
 Type:=xlExcelLinks

Worksheets("Staff Rota 2019").Protect "broncko", DrawingObjects:=True, 
Contents:=True, Scenarios:=True

end sub
Broncko
  • 21
  • 3
  • 2
    Well, avoid select, see https://stackoverflow.com/q/10714251/4961700 – Solar Mike Jan 07 '19 at 13:07
  • Try adding () after each of those calls. – Emily Alden Jan 07 '19 at 13:33
  • @EmilyAlden First, editor will remove these parenthesis. Second, how that would *possibly* help? – JohnyL Jan 07 '19 at 13:57
  • @JohnyL …… I legitimately thought you had to put those there to indicate that you were not pulling in any variables. I apologies that I was incorrect. – Emily Alden Jan 07 '19 at 13:59
  • I don't think the option to enter the password is available when updating links. One option is to open the linked workbook using the password then update the links, then close the linked workbook again. I would not want to use sendkeys to type a password for the very reason you have already come across. – Dave Jan 07 '19 at 14:14
  • @EmilyAlden VBA is a mess when it comes to functions/sub calling. – JohnyL Jan 07 '19 at 14:14
  • Hmm. Really frustrating. All 3 macros do as they are supposed to when ran individually. Trying to run them in sequence at the the click of a button though and the final one doesn't run. Tried removing the select function and replacing with alternatives as in the link but that didn't help. Is there another way other than 'call' to run 3 macros in sequence? – Broncko Jan 08 '19 at 17:28
  • try inserting a `DoEvents` in your For loop...and since nobody has said it yet, your PostSelect sub could simply be typed `Sheets("Sheet1").Range("A1").ClearContents` – MBB70 Jan 15 '19 at 22:37
  • Also, I'm not familiar with LinkSources, but I do know that SendKeys is trouble. There has to be another way. – MBB70 Jan 15 '19 at 22:38
  • Thanks for the suggestions. Still no joy with the DoEvents – Broncko Jan 17 '19 at 10:44
  • The code is so short in pre and post select. Try moving into the `UpDateLinksCode` and just call that sub. And also work directly with the objects. `Sheets("Sheet1").Range("A1").Select` (though this may need to be split in order to function correctly). Also, maybe the findwindow API is a better way to enter the password? – Scott Holtzman Mar 11 '19 at 13:52
  • What happens when you step through the code with the debbuger (`F8`)? – John Alexiou Mar 11 '19 at 18:53

1 Answers1

0

This code must be in a worksheet code instead of a module since you are responding to a button event. The problem is that you select a different worksheet Sheets("Sheet1").Select and there must be some other code that gets run at that point.

Move the code to a module, and reference the specific worksheets directly (do not use the select statement).

for example:

Worksheets("Sheet1").Range("A1").ClearContents
John Alexiou
  • 28,472
  • 11
  • 77
  • 133
  • @Broncko - You should [edit](https://stackoverflow.com/posts/54074991/edit) your question with the new code, and do not supply an answer that isn't a fix for the problem. – John Alexiou Mar 13 '19 at 12:35