Thursday 21 January 2016

Excel VBA updating links

I am trying to set up a VBA macro to update link paths in excel. I looked up some code online and tried to put it together, and am getting errors. I am wondering if i could get some direction here. Please note that i am not a programmer by profession, just trying to reduce some manual updating work.



Cheers!




Private Sub CommandButton1_Click()



Dim FolderPath As String
Dim FSO As Object
Dim bookname As String
Dim wbook As Workbook
Dim oldname As String
Dim newname As String


oldname = "C:\Users\XX\Documents\[Broadstreet.xlsx]"

newname = "C:\Users\XX\Documents\[Broadstreet2.xlsx]"

FolderPath = "C:\Users\XX\Documents1"


With Application
.ScreenUpdating = False
.AskToUpdateLinks = False

End With


For Each Workbook In FSO.GetFolder(FolderPath).Files
bookname = Workbook.Name

MsgBox (bookname)

Set wb = Workbooks.Open(FolderPath & "\" & bookname)


ActiveWorkbook.ChangeLink oldname1, newname1, xlLinkTypeExcelLinks


wb.Close SaveChanges:=True

Next

Application.ScreenUpdating = True



End Sub

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...