Hi Everyone,
So I recently noticed that when I open a file using Workbooks.Open method which has external connections, the connection becomes corrupt or breaks. Does anyone know why this happens/is there a way around it? I want to open the file with external connections, fill in some information & save as a new name & loop through a variable. I will place the code here:
Code
Sub Create_Le()
Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim i As Integer
Dim year As String
Dim per As String
Dim wb As Workbook
Dim le As String
Dim template As Workbook
Dim xFd As FileDialog
i = 1
Do Until IsEmpty(Sheet1.Cells(i, 3))
With template
Set template = Workbooks.Open("L:\FASE\OIE reconciliation Template.xlsm")
'what we change 'what we make it equal too
'company code
Worksheets("Cover Sheet_pdf").Cells(11, 7) = Sheet1.Cells(i, 3)
'year
Worksheets("Cover Sheet_pdf").Cells(8, 7) = Sheet1.Cells(1, 1)
'quarter
Worksheets("Cover Sheet_pdf").Cells(9, 7) = Sheet1.Cells(2, 1)
End With
le = Worksheets("Cover Sheet_pdf").Cells(11, 7)
year = Worksheets("Cover Sheet_pdf").Cells(8, 7)
per = Worksheets("Cover Sheet_pdf").Cells(9, 7)
wbname = le & " OIE reconciliation Q" & per & " " & year & ".xlsm"
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
ActiveWorkbook.SaveAs Filename:=xFd & "\" & wbname, FileFormat:=52
ActiveWorkbook.Close
i = i + 1
Loop
End SubThe code above works fine, except the fact it appears to break the connections.
I could not find anything relevant online.
Please help!
Thank you in advance,
Mel