Mixing
VBA can save workbook to Sharepoint but cannot read it afterwards
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
My VBA code loops through and combine several workbooks into a master workbook if they share the same prefix. E.g. B1.xlsx and B2.xlsx should combine into B Master.xlsx
If a master workbook does not currently exist, code should create one. The issue here is - after successfully creating a master workbook, the code cannot read it again with the Dir() function in the next iteration of the loop. filestring is empty ("") in debug mode, so it tries to create a new master workbook again, when it has already done so in the previous loop.
savedirectory is a sharepoint directory like "\sharepoint.comfolderdocuments".
The code is able to read and open files in the sharepoint directory.
If I had already manually created the master workbook myself, the code is able to open it and copy workbooks into the master. It is only when the code creates this master workbook itself that the error occurs.
I suspect the issue could be time taken to save the master file to sharepoint and re-open it afterwards? I have tried adding a wait for 10s code between each file opening but it does not work.
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
excel vba sharepoint
asked Jan 3 at 3:18
freebeerdfreebeerd
113
add a comment |
My VBA code loops through and combine several workbooks into a master workbook if they share the same prefix. E.g. B1.xlsx and B2.xlsx should combine into B Master.xlsx
If a master workbook does not currently exist, code should create one. The issue here is - after successfully creating a master workbook, the code cannot read it again with the Dir() function in the next iteration of the loop. filestring is empty ("") in debug mode, so it tries to create a new master workbook again, when it has already done so in the previous loop.
savedirectory is a sharepoint directory like "\sharepoint.comfolderdocuments".
The code is able to read and open files in the sharepoint directory.
If I had already manually created the master workbook myself, the code is able to open it and copy workbooks into the master. It is only when the code creates this master workbook itself that the error occurs.
I suspect the issue could be time taken to save the master file to sharepoint and re-open it afterwards? I have tried adding a wait for 10s code between each file opening but it does not work.
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
excel vba sharepoint
asked Jan 3 at 3:18
freebeerdfreebeerd
113
you could addDoEventsalong with theApplication.Wait (Now + TimeValue("0:00:05"))
– Zack E
Jan 3 at 14:09
add a comment |
My VBA code loops through and combine several workbooks into a master workbook if they share the same prefix. E.g. B1.xlsx and B2.xlsx should combine into B Master.xlsx
If a master workbook does not currently exist, code should create one. The issue here is - after successfully creating a master workbook, the code cannot read it again with the Dir() function in the next iteration of the loop. filestring is empty ("") in debug mode, so it tries to create a new master workbook again, when it has already done so in the previous loop.
savedirectory is a sharepoint directory like "\sharepoint.comfolderdocuments".
The code is able to read and open files in the sharepoint directory.
If I had already manually created the master workbook myself, the code is able to open it and copy workbooks into the master. It is only when the code creates this master workbook itself that the error occurs.
I suspect the issue could be time taken to save the master file to sharepoint and re-open it afterwards? I have tried adding a wait for 10s code between each file opening but it does not work.
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
excel vba sharepoint
asked Jan 3 at 3:18
freebeerdfreebeerd
113
My VBA code loops through and combine several workbooks into a master workbook if they share the same prefix. E.g. B1.xlsx and B2.xlsx should combine into B Master.xlsx
If a master workbook does not currently exist, code should create one. The issue here is - after successfully creating a master workbook, the code cannot read it again with the Dir() function in the next iteration of the loop. filestring is empty ("") in debug mode, so it tries to create a new master workbook again, when it has already done so in the previous loop.
savedirectory is a sharepoint directory like "\sharepoint.comfolderdocuments".
The code is able to read and open files in the sharepoint directory.
If I had already manually created the master workbook myself, the code is able to open it and copy workbooks into the master. It is only when the code creates this master workbook itself that the error occurs.
I suspect the issue could be time taken to save the master file to sharepoint and re-open it afterwards? I have tried adding a wait for 10s code between each file opening but it does not work.
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
excel vba sharepoint
excel vba sharepoint
asked Jan 3 at 3:18
freebeerdfreebeerd
113
asked Jan 3 at 3:18
freebeerdfreebeerd
113
asked Jan 3 at 3:18
freebeerdfreebeerd
113
asked Jan 3 at 3:18
freebeerdfreebeerd
113
asked Jan 3 at 3:18
freebeerdfreebeerd
113
113
you could addDoEventsalong with theApplication.Wait (Now + TimeValue("0:00:05"))
– Zack E
Jan 3 at 14:09
add a comment |
you could addDoEventsalong with theApplication.Wait (Now + TimeValue("0:00:05"))
– Zack E
Jan 3 at 14:09
you could add
DoEvents along with the Application.Wait (Now + TimeValue("0:00:05"))– Zack E
Jan 3 at 14:09
you could add
DoEvents along with the Application.Wait (Now + TimeValue("0:00:05"))– Zack E
Jan 3 at 14:09
add a comment |
1 Answer
1
active
oldest
votes
Try adding the DoEvents along with the Application.Wait
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
DoEvents
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
answered Jan 3 at 14:17
Zack EZack E
425115
add a comment |
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
});
}
});
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%2f54015894%2fvba-can-save-workbook-to-sharepoint-but-cannot-read-it-afterwards%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try adding the DoEvents along with the Application.Wait
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
DoEvents
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
answered Jan 3 at 14:17
Zack EZack E
425115
add a comment |
Try adding the DoEvents along with the Application.Wait
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
DoEvents
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
answered Jan 3 at 14:17
Zack EZack E
425115
add a comment |
Try adding the DoEvents along with the Application.Wait
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
DoEvents
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
answered Jan 3 at 14:17
Zack EZack E
425115
Try adding the DoEvents along with the Application.Wait
For j = LBound(filearray) to UBound(filearray)
name = filearray(j)
filestring = Dir(savedirectory & "" & name & " master.xlsx")
If Len(filestring) = 0 Then
'Only 1 sheet needed in new workbook to start
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 3
'Save newly created workbook
wb.Sheets(1).Name = "Placeholder"
wb.Application.DisplayAlerts = False
DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
DoEvents
wb.SaveAs Filename:=savedirectory & "" & name & " master", FileFormat:=xlOpenXMLWorkbook
wb.Application.DisplayAlerts = True
Debug.Print "Created new file, " & name & " master.xlsx"
wb.Close
End If
'Open master workbook and do other things
Next j
answered Jan 3 at 14:17
Zack EZack E
425115
answered Jan 3 at 14:17
Zack EZack E
425115
answered Jan 3 at 14:17
Zack EZack E
425115
answered Jan 3 at 14:17
Zack EZack E
425115
425115
add a comment |
add a comment |
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.
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%2f54015894%2fvba-can-save-workbook-to-sharepoint-but-cannot-read-it-afterwards%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
you could add
DoEventsalong with theApplication.Wait (Now + TimeValue("0:00:05"))– Zack E
Jan 3 at 14:09