Mixing
How to keep links to other open files when saving them by VBA?
0
I'm trying to open few files with connection between them and save them with a different names, so the links will update to there new names.
My code open the files and save them in there new names but don't change the links to the new names.
Sub PullFromFile()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For x = 1 To tbl.Range.Rows.Count - 1
' Get path from cell A1 on Report tab
openPatch = tbl.DataBodyRange(x, 1)
openfile = tbl.DataBodyRange(x, 2)
' Make sure there is a backslash at the end of the from path
If Right(openPatch, 1) <> "" Then openPatch = openPatch & ""
Set wkbFrom = Workbooks.Open(openPatch & openfile)
'set "newwkb" & x = ThisWorkbook
wkb.Activate
Next x
MsgBox ("Files Opened. Press next button to save the files in new names")
End Sub
Sub SaveTheFiles()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For y = 1 To tbl.Range.Rows.Count - 1
openWin = tbl.DataBodyRange(y, 2)
Windows(openWin).Activate
' Get path from cell A1 on Report tab
savePatch = tbl.DataBodyRange(y, 3)
savefile = tbl.DataBodyRange(y, 4)
' Make sure there is a backslash at the end of the from path
If Right(savePatch, 1) <> "" Then savePatch = savePatch & ""
'save as
ActiveWorkbook.SaveAs Filename:=(savePatch & savefile), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Next y
wkb.Activate
End Sub
I want the links to be changed to the new files names.
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
asked Jan 1 at 15:52
RoeeRoee
11
add a comment |
0
I'm trying to open few files with connection between them and save them with a different names, so the links will update to there new names.
My code open the files and save them in there new names but don't change the links to the new names.
Sub PullFromFile()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For x = 1 To tbl.Range.Rows.Count - 1
' Get path from cell A1 on Report tab
openPatch = tbl.DataBodyRange(x, 1)
openfile = tbl.DataBodyRange(x, 2)
' Make sure there is a backslash at the end of the from path
If Right(openPatch, 1) <> "" Then openPatch = openPatch & ""
Set wkbFrom = Workbooks.Open(openPatch & openfile)
'set "newwkb" & x = ThisWorkbook
wkb.Activate
Next x
MsgBox ("Files Opened. Press next button to save the files in new names")
End Sub
Sub SaveTheFiles()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For y = 1 To tbl.Range.Rows.Count - 1
openWin = tbl.DataBodyRange(y, 2)
Windows(openWin).Activate
' Get path from cell A1 on Report tab
savePatch = tbl.DataBodyRange(y, 3)
savefile = tbl.DataBodyRange(y, 4)
' Make sure there is a backslash at the end of the from path
If Right(savePatch, 1) <> "" Then savePatch = savePatch & ""
'save as
ActiveWorkbook.SaveAs Filename:=(savePatch & savefile), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Next y
wkb.Activate
End Sub
I want the links to be changed to the new files names.
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
asked Jan 1 at 15:52
RoeeRoee
11
add a comment |
0
0
0
I'm trying to open few files with connection between them and save them with a different names, so the links will update to there new names.
My code open the files and save them in there new names but don't change the links to the new names.
Sub PullFromFile()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For x = 1 To tbl.Range.Rows.Count - 1
' Get path from cell A1 on Report tab
openPatch = tbl.DataBodyRange(x, 1)
openfile = tbl.DataBodyRange(x, 2)
' Make sure there is a backslash at the end of the from path
If Right(openPatch, 1) <> "" Then openPatch = openPatch & ""
Set wkbFrom = Workbooks.Open(openPatch & openfile)
'set "newwkb" & x = ThisWorkbook
wkb.Activate
Next x
MsgBox ("Files Opened. Press next button to save the files in new names")
End Sub
Sub SaveTheFiles()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For y = 1 To tbl.Range.Rows.Count - 1
openWin = tbl.DataBodyRange(y, 2)
Windows(openWin).Activate
' Get path from cell A1 on Report tab
savePatch = tbl.DataBodyRange(y, 3)
savefile = tbl.DataBodyRange(y, 4)
' Make sure there is a backslash at the end of the from path
If Right(savePatch, 1) <> "" Then savePatch = savePatch & ""
'save as
ActiveWorkbook.SaveAs Filename:=(savePatch & savefile), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Next y
wkb.Activate
End Sub
I want the links to be changed to the new files names.
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
asked Jan 1 at 15:52
RoeeRoee
11
I'm trying to open few files with connection between them and save them with a different names, so the links will update to there new names.
My code open the files and save them in there new names but don't change the links to the new names.
Sub PullFromFile()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For x = 1 To tbl.Range.Rows.Count - 1
' Get path from cell A1 on Report tab
openPatch = tbl.DataBodyRange(x, 1)
openfile = tbl.DataBodyRange(x, 2)
' Make sure there is a backslash at the end of the from path
If Right(openPatch, 1) <> "" Then openPatch = openPatch & ""
Set wkbFrom = Workbooks.Open(openPatch & openfile)
'set "newwkb" & x = ThisWorkbook
wkb.Activate
Next x
MsgBox ("Files Opened. Press next button to save the files in new names")
End Sub
Sub SaveTheFiles()
Dim wkb As Workbook, wkbFrom As Workbook, wbkto As Workbook
Dim openfile As String
Dim openPatch As String
Dim savefile As String
Dim savePatch As String
Dim openWin As String
Dim closeWin As String
Dim tbl As ListObject
Dim x As Long
Dim y As Long
Dim z As Long
Application.DisplayAlerts = False
Set wkb = ThisWorkbook
Set tbl = ActiveSheet.ListObjects("RoeeTbl")
For y = 1 To tbl.Range.Rows.Count - 1
openWin = tbl.DataBodyRange(y, 2)
Windows(openWin).Activate
' Get path from cell A1 on Report tab
savePatch = tbl.DataBodyRange(y, 3)
savefile = tbl.DataBodyRange(y, 4)
' Make sure there is a backslash at the end of the from path
If Right(savePatch, 1) <> "" Then savePatch = savePatch & ""
'save as
ActiveWorkbook.SaveAs Filename:=(savePatch & savefile), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Next y
wkb.Activate
End Sub
I want the links to be changed to the new files names.
excel vba excel-vba
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
asked Jan 1 at 15:52
RoeeRoee
11
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
asked Jan 1 at 15:52
RoeeRoee
11
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
edited Jan 7 at 8:53
Pᴇʜ
23.8k62952
23.8k62952
asked Jan 1 at 15:52
RoeeRoee
11
asked Jan 1 at 15:52
RoeeRoee
11
asked Jan 1 at 15:52
RoeeRoee
11
11
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53996830%2fhow-to-keep-links-to-other-open-files-when-saving-them-by-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53996830%2fhow-to-keep-links-to-other-open-files-when-saving-them-by-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
