PDA

View Full Version : Open and Link Spreadsheet Using VBA



mlo356
10-09-2016, 10:25 PM
Hi All, I have this macro that first opens another excel spreadsheet and then links/maps the opened spreadsheet to the spreadsheet in which the macro is running from. The reason for this is because the data links faster if the source document is open. I labeled the code as much as possible to help as you read through it.

A. The first part of the code opens a dialog box to select and open the spreadsheet that I want to link.
B. The second part of the code opens a second dialog box in which I will select and link the same spreadsheet as previous

I would like to accomplish this all in once step.

1. I would like to open and then link the selected document.
2. I would also like to close the opened file at the end of the macro (as the last step).

Partial Code below. Any help would be greatly appreciated!! Thanks All!


Private Sub OpenAndLink()

' Part 1: Open File To Be Linked

ChDrive "Y"
ChDir "Y:\User"
FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Open Database File")

If FileToOpen <> False Then
Workbooks.Open FileToOpen

End If

' Part 2: Link Same File as Part 1

ChDrive "Y"
ChDir "Y:\User"
alink = ThisWorkbook.LinkSources

If IsEmpty(alink) Then
MsgBox "No links found"

ElseIf UBound(alink) = 1 Then
Application.Dialogs(xlDialogChangeLink).Show alink(1), "", 1

Else
Application.Dialogs(xlDialogOpenLinks).Show

End If

End Sub