I am running office 365 home on windows 10 both latest versions. I have a project that requires the workbook ("Diddly.xlsm") data to be archived every 6 months to a code generated file name leaving t...
SELECT A,B,C,D,CAST(Edate AS DATE) AS EDate,FAmt,G, 'Current Amount is > 80% from last timeline' AS Comments FROM
(
SELECT A,B,C,D,Edate,FAmt,G,
IIF
(
LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt <> 0.00, --If previous value is ZERO and the last one is not, the difference can be considered as >80%
999999,
IIF
(
LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) = 0.00 AND FAmt = 0.00, --If previous value and the last one both ZERO, the difference is ZERO
0,
FAmt/LEAD(FAmt, 1, -1) OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) - 1 --OR calculate as normal way
)
)
AS P,
ROW_NUMBER() OVER(PARTITION BY A,B,C,D ORDER BY EDATE DESC) AS RN
FROM #AR
)T
WHERE
P>0.8 AND RN = 1
JKPieterse I have this same problem, and I do have the full path as you said. I can read from my OneDrive, but when I go to save it fails. It works fine locally. Here is a snippet:
Dim wb_NewData As Workbook Set wb_NewData = openWorkbook("Choose a workbook to reformat")
You have to rebuild the local path by replacing "https://.../.../" with local path. To do this you can get the OneDrive Path from the local environnement in vba with ENVIRON command => ENVIRON("OneDrive") return the local path of OneDrive
Example here. Adapt it as you need 🙂
Public Function fRelocateOneDrivePath(strParamURIPath As String) As String
Dim strPath As String Dim I As Integer
' Replace all "/" by "\" if exists strPath = Replace(strParamURIPath, "/", "\")
After a lot of head scratching I have found a way round the CopyAs issue. The workbooks attached contain macros stripped down fron the full coding in my main application. My AppBook.xlsm opens an "auxiliary" workbook AuxBook.xlsm which then SaveAs AppBook.xlsm to ArcBook.xlsm in (in my case) the Archive folder and returns to AppBook.xlsm which then runs the macro ClearData. I hope this may be relevant to your issue
Can't find means of attaching workbooks so the code is below:
Dim lcDiddlyFile, lcDiddlyPath, lcArchFile, lcArchPath As String Dim lvAns As Variant Application.DisplayAlerts = False Application.ScreenUpdating = False lcDiddlyPath = Application.ActiveWorkbook.Path & "\" lcDiddlyFile = ActiveWorkbook.Name lcArchPath = lcDiddlyPath & "Archive\" lcArchFile = "ArcBook.xlsm" Application.Workbooks("AppBook.xlsm").SaveAs FileName:=(lcArchPath & lcArchFile) ActiveWorkbook.Save
End Sub 'Do Archive
Resources
"}},"componentScriptGroups({\"componentId\":\"custom.widget.MicrosoftFooter\"})":{"__typename":"ComponentScriptGroups","scriptGroups":{"__typename":"ComponentScriptGroupsDefinition","afterInteractive":{"__typename":"PageScriptGroupDefinition","group":"AFTER_INTERACTIVE","scriptIds":[]},"lazyOnLoad":{"__typename":"PageScriptGroupDefinition","group":"LAZY_ON_LOAD","scriptIds":[]}},"componentScripts":[]},"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/community/NavbarDropdownToggle\"]})":[{"__ref":"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/common/QueryHandler\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/common/QueryHandler-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/EscalatedMessageBanner\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/EscalatedMessageBanner-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/users/UserLink\"]})":[{"__ref":"CachedAsset:text:en_US-components/users/UserLink-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserRank\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserRank-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageTime\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageTime-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSolvedBadge\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSolvedBadge-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSubject\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSubject-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageBody\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageBody-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageCustomFields\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageCustomFields-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageReplyButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageReplyButton-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/MessageSolutionList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/MessageSolutionList-1745505309803"}],"message({\"id\":\"message:3124294\"})":{"__ref":"ForumTopicMessage:message:3124294"},"messages({\"constraints\":{\"solution\":{\"eq\":true},\"topicId\":{\"eq\":\"message:3124294\"}},\"first\":10,\"sorts\":{\"postTime\":{\"direction\":\"ASC\"}}})":{"__typename":"MessageConnection","edges":[{"__typename":"MessageEdge","cursor":"MjUuMXwyLjF8aXwxMHwxMzI6MXxpbnQsMzE0MjgzMSwzMTQyODMx","node":{"__ref":"AcceptedSolutionMessage:message:3142831"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null},"totalCount":1},"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserAvatar\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/ranks/UserRankLabel\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/AcceptedSolutionButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309803"}],"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/LinearReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505309803"}],"message({\"id\":\"message:3142831\"})":{"__ref":"AcceptedSolutionMessage:message:3142831"},"message({\"id\":\"message:3144221\"})":{"__ref":"ForumReplyMessage:message:3144221"},"cachedText({\"lastModified\":\"1745505309803\",\"locale\":\"en-US\",\"namespaces\":[\"components/tags/TagView/TagViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309803"}],"message({\"id\":\"message:3747711\"})":{"__ref":"ForumReplyMessage:message:3747711"},"message({\"id\":\"message:3722795\"})":{"__ref":"ForumReplyMessage:message:3722795"}},"CachedAsset:pages-1745483080879":{"__typename":"CachedAsset","id":"pages-1745483080879","value":[{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"BlogViewAllPostsPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId/all-posts/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CasePortalPage","type":"CASE_PORTAL","urlPath":"/caseportal","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CreateGroupHubPage","type":"GROUP_HUB","urlPath":"/groups/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CaseViewPage","type":"CASE_DETAILS","urlPath":"/case/:caseId/:caseNumber","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"InboxPage","type":"COMMUNITY","urlPath":"/inbox","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"HelpFAQPage","type":"COMMUNITY","urlPath":"/help","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"IdeaMessagePage","type":"IDEA_POST","urlPath":"/idea/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"IdeaViewAllIdeasPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/all-ideas/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"LoginPage","type":"USER","urlPath":"/signin","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"BlogPostPage","type":"BLOG","urlPath":"/category/:categoryId/blogs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"UserBlogPermissions.Page","type":"COMMUNITY","urlPath":"/c/user-blog-permissions/page","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ThemeEditorPage","type":"COMMUNITY","urlPath":"/designer/themes","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TkbViewAllArticlesPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId/all-articles/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllEvents","type":"CUSTOM","urlPath":"/Events","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"OccasionEditPage","type":"EVENT","urlPath":"/event/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"OAuthAuthorizationAllowPage","type":"USER","urlPath":"/auth/authorize/allow","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"PageEditorPage","type":"COMMUNITY","urlPath":"/designer/pages","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"PostPage","type":"COMMUNITY","urlPath":"/category/:categoryId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForumBoardPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TkbBoardPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"EventPostPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"UserBadgesPage","type":"COMMUNITY","urlPath":"/users/:login/:userId/badges","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"GroupHubMembershipAction","type":"GROUP_HUB","urlPath":"/membership/join/:nodeId/:membershipType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"MaintenancePage","type":"COMMUNITY","urlPath":"/maintenance","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"IdeaReplyPage","type":"IDEA_REPLY","urlPath":"/idea/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"UserSettingsPage","type":"USER","urlPath":"/mysettings/:userSettingsTab","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"GroupHubsPage","type":"GROUP_HUB","urlPath":"/groups","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForumPostPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"OccasionRsvpActionPage","type":"OCCASION","urlPath":"/event/:boardId/:messageSubject/:messageId/rsvp/:responseType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"VerifyUserEmailPage","type":"USER","urlPath":"/verifyemail/:userId/:verifyEmailToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"AllOccasionsPage","type":"OCCASION","urlPath":"/category/:categoryId/events/:boardId/all-events/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"EventBoardPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TkbReplyPage","type":"TKB_REPLY","urlPath":"/kb/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"IdeaBoardPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CommunityGuideLinesPage","type":"COMMUNITY","urlPath":"/communityguidelines","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CaseCreatePage","type":"SALESFORCE_CASE_CREATION","urlPath":"/caseportal/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TkbEditPage","type":"TKB","urlPath":"/kb/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForgotPasswordPage","type":"USER","urlPath":"/forgotpassword","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"IdeaEditPage","type":"IDEA","urlPath":"/idea/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TagPage","type":"COMMUNITY","urlPath":"/tag/:tagName","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"BlogBoardPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"OccasionMessagePage","type":"OCCASION_TOPIC","urlPath":"/event/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ManageContentPage","type":"COMMUNITY","urlPath":"/managecontent","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ClosedMembershipNodeNonMembersPage","type":"GROUP_HUB","urlPath":"/closedgroup/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CommunityPage","type":"COMMUNITY","urlPath":"/","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForumMessagePage","type":"FORUM_TOPIC","urlPath":"/discussions/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"IdeaPostPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"CommunityHub.Page","type":"CUSTOM","urlPath":"/Directory","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"BlogMessagePage","type":"BLOG_ARTICLE","urlPath":"/blog/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"RegistrationPage","type":"USER","urlPath":"/register","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"EditGroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForumEditPage","type":"FORUM","urlPath":"/discussions/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ResetPasswordPage","type":"USER","urlPath":"/resetpassword/:userId/:resetPasswordToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllBlogs.Page","type":"CUSTOM","urlPath":"/blogs","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TkbMessagePage","type":"TKB_ARTICLE","urlPath":"/kb/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"BlogEditPage","type":"BLOG","urlPath":"/blog/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ManageUsersPage","type":"USER","urlPath":"/users/manage/:tab?/:manageUsersTab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForumReplyPage","type":"FORUM_REPLY","urlPath":"/discussions/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"PrivacyPolicyPage","type":"COMMUNITY","urlPath":"/privacypolicy","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"NotificationPage","type":"COMMUNITY","urlPath":"/notifications","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"UserPage","type":"USER","urlPath":"/users/:login/:userId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"OccasionReplyPage","type":"OCCASION_REPLY","urlPath":"/event/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ManageMembersPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/manage/:tab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"SearchResultsPage","type":"COMMUNITY","urlPath":"/search","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"BlogReplyPage","type":"BLOG_REPLY","urlPath":"/blog/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"GroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TermsOfServicePage","type":"COMMUNITY","urlPath":"/termsofservice","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"CategoryPage","type":"CATEGORY","urlPath":"/category/:categoryId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"ForumViewAllTopicsPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/all-topics/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"TkbPostPage","type":"TKB","urlPath":"/category/:categoryId/kbs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1745483080879,"localOverride":null,"page":{"id":"GroupHubPostPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"}],"localOverride":false},"CachedAsset:text:en_US-components/context/AppContext/AppContextProvider-0":{"__typename":"CachedAsset","id":"text:en_US-components/context/AppContext/AppContextProvider-0","value":{"noCommunity":"Cannot find community","noUser":"Cannot find current user","noNode":"Cannot find node with id {nodeId}","noMessage":"Cannot find message with id {messageId}"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Loading/LoadingDot-0":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Loading/LoadingDot-0","value":{"title":"Loading..."},"localOverride":false},"User:user:-1":{"__typename":"User","id":"user:-1","uid":-1,"login":"Deleted","email":"","avatar":null,"rank":null,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":"ANONYMOUS","registrationTime":null,"confirmEmailStatus":false,"registrationAccessLevel":"VIEW","ssoRegistrationFields":[]},"ssoId":null,"profileSettings":{"__typename":"ProfileSettings","dateDisplayStyle":{"__typename":"InheritableStringSettingWithPossibleValues","key":"layout.friendly_dates_enabled","value":"false","localValue":"true","possibleValues":["true","false"]},"dateDisplayFormat":{"__typename":"InheritableStringSetting","key":"layout.format_pattern_date","value":"MMM dd yyyy","localValue":"MM-dd-yyyy"},"language":{"__typename":"InheritableStringSettingWithPossibleValues","key":"profile.language","value":"en-US","localValue":"en","possibleValues":["en-US"]}},"deleted":false},"Theme:customTheme1":{"__typename":"Theme","id":"customTheme1"},"Category:category:microsoft365":{"__typename":"Category","id":"category:microsoft365","entityType":"CATEGORY","displayId":"microsoft365","nodeType":"category","depth":3,"title":"Microsoft 365","shortTitle":"Microsoft 365","parent":{"__ref":"Category:category:products-services"},"categoryPolicies":{"__typename":"CategoryPolicies","canReadNode":{"__typename":"PolicyResult","failureReason":null}}},"Category:category:top":{"__typename":"Category","id":"category:top","displayId":"top","nodeType":"category","depth":0,"title":"Top","entityType":"CATEGORY","shortTitle":"Top"},"Category:category:communities":{"__typename":"Category","id":"category:communities","displayId":"communities","nodeType":"category","depth":1,"parent":{"__ref":"Category:category:top"},"title":"Communities","entityType":"CATEGORY","shortTitle":"Communities"},"Category:category:products-services":{"__typename":"Category","id":"category:products-services","displayId":"products-services","nodeType":"category","depth":2,"parent":{"__ref":"Category:category:communities"},"title":"Products","entityType":"CATEGORY","shortTitle":"Products"},"Forum:board:ExcelGeneral":{"__typename":"Forum","id":"board:ExcelGeneral","entityType":"FORUM","displayId":"ExcelGeneral","nodeType":"board","depth":4,"conversationStyle":"FORUM","title":"Excel","description":"Your community for how-to discussions and sharing best practices on Microsoft Excel. If you’re looking for technical support, please visit Microsoft Support Community.","avatar":null,"profileSettings":{"__typename":"ProfileSettings","language":null},"parent":{"__ref":"Category:category:microsoft365"},"ancestors":{"__typename":"CoreNodeConnection","edges":[{"__typename":"CoreNodeEdge","node":{"__ref":"Community:community:gxcuf89792"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:communities"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:products-services"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:microsoft365"}}]},"userContext":{"__typename":"NodeUserContext","canAddAttachments":false,"canUpdateNode":false,"canPostMessages":false,"isSubscribed":false},"boardPolicies":{"__typename":"BoardPolicies","canPublishArticleOnCreate":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","key":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","args":[]}}},"shortTitle":"Excel","repliesProperties":{"__typename":"RepliesProperties","sortOrder":"REVERSE_PUBLISH_TIME","repliesFormat":"threaded"},"tagProperties":{"__typename":"TagNodeProperties","tagsEnabled":{"__typename":"PolicyResult","failureReason":null}},"requireTags":true,"tagType":"PRESET_ONLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/"},"Rank:rank:37":{"__typename":"Rank","id":"rank:37","position":18,"name":"Copper Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"User:user:372045":{"__typename":"User","id":"user:372045","uid":372045,"login":"coltartjmcsa3","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-4.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"email":"","messagesCount":10,"biography":null,"topicsCount":2,"kudosReceivedCount":0,"kudosGivenCount":0,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2019-07-06T08:56:05.720-07:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0,"entityType":"USER","eventPath":"community:gxcuf89792/user:372045"},"ForumTopicMessage:message:3124294":{"__typename":"ForumTopicMessage","uid":3124294,"subject":"Excel VBA and OneDrive SaveCopyAs","id":"message:3124294","revisionNum":1,"repliesCount":14,"author":{"__ref":"User:user:372045"},"depth":0,"hasGivenKudo":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3124294"},"readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3124294"},"body@stripHtml({\"truncateLength\":200})":" I am running office 365 home on windows 10 both latest versions. I have a project that requires the workbook (\"Diddly.xlsm\") data to be archived every 6 months to a code generated file name leaving t...","body@stringLength":"1975","rawBody":"
I am running office 365 home on windows 10 both latest versions. I have a project that requires the workbook (\"Diddly.xlsm\") data to be archived every 6 months to a code generated file name leaving the original file clear of data. The project was developed using workstation (Acer Laptop) based files and worked as expected. I needed to allow others share access so started to use OneDrive. Opening the workbook from OneDrive generated an error at the line ActiveWorkbook.SaveCopyAs lcfile. Looking at the file path obtained not surprising.
My question is how can I code to obtain a valid file save path when using the online version of the workbook OR is there another way to what I want?
The test code is:
Sub test() Dim lcfilesavepath, lcfile As String Protection (False) Application.DisplayAlerts = False lcfilesavepath = Application.ThisWorkbook.Path & \"\\Archive\\\" 'Opening the diddly file from LapTopUser with OneDrive closed gives lcfilesavepath=C:\\Users\\Owner\\OneDrive\\Diddly_Share\\Archive\\ 'lcfilesavepath = \"\" 'If lcfilesavepath is not used the file is saved to C:\\Users\\Owner\\OneDrive\\Documents lcfile = lcfilesavepath & Range(\"Title\").Value & \".xlsm\" 'If the diddly file is opened from OneDrive the Filesavepath = https://d.docs.live.net/61298770340f/Diddly_Share|Archive 'and the next line fails using lcfilesavepath https:\\\\... or null ActiveWorkbook.SaveCopyAs lcfile lvans = MsgBox(\"The workbook has been saved to file \" & lcfile) Application.DisplayAlerts = True Protection (True)
End Sub
","kudosSumWeight":0,"postTime":"2022-02-06T22:10:17.508-08:00","images":{"__typename":"AssociatedImageConnection","edges":[],"totalCount":0,"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"attachments":{"__typename":"AttachmentConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"tags":{"__typename":"TagConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"TagEdge","cursor":"MjUuMXwyLjF8b3wxMHxfTlZffDE","node":{"__typename":"Tag","id":"tag:excel","text":"excel","time":"2016-06-21T14:08:51.129-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}},{"__typename":"TagEdge","cursor":"MjUuMXwyLjF8b3wxMHxfTlZffDI","node":{"__typename":"Tag","id":"tag:Macros and VBA","text":"Macros and VBA","time":"2016-06-24T14:00:56.974-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}},{"__typename":"TagEdge","cursor":"MjUuMXwyLjF8b3wxMHxfTlZffDM","node":{"__typename":"Tag","id":"tag:office 365","text":"office 365","time":"2016-06-27T01:27:02.990-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}}]},"timeToRead":1,"currentRevision":{"__ref":"Revision:revision:3124294_1"},"latestVersion":null,"metrics":{"__typename":"MessageMetrics","views":25882},"visibilityScope":"PUBLIC","canonicalUrl":null,"seoTitle":null,"seoDescription":null,"isEscalated":null,"placeholder":false,"originalMessageForPlaceholder":null,"messagePolicies":{"__typename":"MessagePolicies","canModerateSpamMessage":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","key":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","args":[]}}},"archivalData":null,"customFields":[]},"Conversation:conversation:3124294":{"__typename":"Conversation","id":"conversation:3124294","solved":true,"topic":{"__ref":"ForumTopicMessage:message:3124294"},"lastPostingActivityTime":"2023-02-20T09:46:36.752-08:00","lastPostTime":"2023-02-20T09:46:36.752-08:00","unreadReplyCount":14,"isSubscribed":false},"ModerationData:moderation_data:3124294":{"__typename":"ModerationData","id":"moderation_data:3124294","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"Revision:revision:3124294_1":{"__typename":"Revision","id":"revision:3124294_1","lastEditTime":"2022-02-06T22:10:17.508-08:00"},"ForumReplyMessage:message:3719363":{"__typename":"ForumReplyMessage","id":"message:3719363","conversation":{"__ref":"Conversation:conversation:3124294"},"author":{"__ref":"User:user:1695123"},"revisionNum":1,"uid":3719363,"depth":10,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"subject":"Re: Excel VBA and OneDrive SaveCopyAs","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3719363"},"parent":{"__ref":"ForumReplyMessage:message:3144221"},"body":"
JKPieterse I have this same problem, and I do have the full path as you said. I can read from my OneDrive, but when I go to save it fails. It works fine locally. Here is a snippet:
Dim wb_NewData As Workbook Set wb_NewData = openWorkbook(\"Choose a workbook to reformat\")
I have this same problem, and I do have the full path as you said. I can read from my OneDrive, but when I go to save it fails. It works fine locally. Here is a snippet:
Dim wb_NewData As Workbook Set wb_NewData = openWorkbook(\"Choose a workbook to reformat\")
I can't apologise enough. This will be my third try at sending you the correct info. The code works when opening the Excel on my laptop when it has access to the resident file locations. When I open Excel from the online view of OneDrive the code fails with the message shown in the attached .gif. I don't see a way round this and I may have to be satisfied by making the archive file \"manually\" using the Save As option resident in Excel. As I sometimes reply on others to operate the workbook I would have preferred coding. Perhaps I need to find another route to a solution.
","body@stringLength":"797","rawBody":"
I can't apologise enough. This will be my third try at sending you the correct info. The code works when opening the Excel on my laptop when it has access to the resident file locations. When I open Excel from the online view of OneDrive the code fails with the message shown in the attached .gif. I don't see a way round this and I may have to be satisfied by making the archive file \"manually\" using the Save As option resident in Excel. As I sometimes reply on others to operate the workbook I would have preferred coding. Perhaps I need to find another route to a solution.
","isEscalated":null,"postTime":"2022-02-09T02:11:21.325-08:00"},"ModerationData:moderation_data:3142831":{"__typename":"ModerationData","id":"moderation_data:3142831","status":"APPROVED","rejectReason":null},"AcceptedSolutionMessage:message:3142831":{"__typename":"AcceptedSolutionMessage","author":{"__ref":"User:user:22322"},"id":"message:3142831","revisionNum":1,"uid":3142831,"depth":7,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:3142036"},"conversation":{"__ref":"Conversation:conversation:3124294"},"subject":"Re: Excel VBA and OneDrive SaveCopyAs","moderationData":{"__ref":"ModerationData:moderation_data:3142831"},"body":"I don't know what the current value of lcFile is when the error occurs?
If you pass just a file name (no path) to the SaveAs method, Excel assumes it needs to be saved in the active directory. Judging by the error message you showed that seems to be c:\\Windows. Users are not allowed to save files there. You must provide the SaveAs method with both the path and a filename.","body@stripHtml({\"removeProcessingText\":true,\"removeSpoilerMarkup\":true,\"removeTocMarkup\":true,\"truncateLength\":200})@stringLength":"203","postTime":"2022-02-09T04:35:49.385-08:00","lastPublishTime":"2022-02-09T04:35:49.385-08:00","images":{"__typename":"AssociatedImageConnection","edges":[],"totalCount":0,"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"attachments":{"__typename":"AttachmentConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"solution":true,"metrics":{"__typename":"MessageMetrics","views":25588},"placeholder":false,"originalMessageForPlaceholder":null,"videos":{"__typename":"VideoConnection","edges":[],"totalCount":0,"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"isEscalated":null,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:3124294/message:3142831","readOnly":false,"editFrozen":false,"body@stringLength":"385","rawBody":"I don't know what the current value of lcFile is when the error occurs?
If you pass just a file name (no path) to the SaveAs method, Excel assumes it needs to be saved in the active directory. Judging by the error message you showed that seems to be c:\\Windows. Users are not allowed to save files there. You must provide the SaveAs method with both the path and a filename.","customFields":[]},"User:user:1695123":{"__typename":"User","id":"user:1695123","uid":1695123,"login":"Mark_Kessler","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-9.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"email":"","messagesCount":1,"biography":null,"topicsCount":0,"kudosReceivedCount":0,"kudosGivenCount":0,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2023-01-18T11:33:21.909-08:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0},"ModerationData:moderation_data:3719363":{"__typename":"ModerationData","id":"moderation_data:3719363","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ModerationData:moderation_data:3144221":{"__typename":"ModerationData","id":"moderation_data:3144221","status":"APPROVED","rejectReason":null},"Rank:rank:32":{"__typename":"Rank","id":"rank:32","position":13,"name":"Silver Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"ModerationData:moderation_data:3143259":{"__typename":"ModerationData","id":"moderation_data:3143259","status":"APPROVED","rejectReason":null},"ModerationData:moderation_data:3142036":{"__typename":"ModerationData","id":"moderation_data:3142036","status":"APPROVED","rejectReason":null},"ForumReplyMessage:message:3143259":{"__typename":"ForumReplyMessage","id":"message:3143259","revisionNum":1,"uid":3143259,"depth":8,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3124294"},"subject":"Re: Excel VBA and OneDrive SaveCopyAs","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3143259"},"body":"
Yes I understand all that. The problem is that the sharer would have to provide the path (we could agree one in advance) and then move the file into OneDrive \"manually\" (can't see a way to provide a path to OneDrive). As I said earlier I need to find another way round this.
I appreciate your help and have gained some more knowledge on the way.
This is my first time of posting on this site so don't know how to close this out.
","body@stringLength":"660","rawBody":"
Yes I understand all that. The problem is that the sharer would have to provide the path (we could agree one in advance) and then move the file into OneDrive \"manually\" (can't see a way to provide a path to OneDrive). As I said earlier I need to find another way round this.
I appreciate your help and have gained some more knowledge on the way.
This is my first time of posting on this site so don't know how to close this out.
","author":{"__ref":"User:user:372045"},"isEscalated":null,"postTime":"2022-02-09T06:19:02.862-08:00","parent":{"__ref":"AcceptedSolutionMessage:message:3142831"}},"ForumReplyMessage:message:3144221":{"__typename":"ForumReplyMessage","id":"message:3144221","revisionNum":1,"uid":3144221,"depth":9,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:3124294"},"subject":"Re: Excel VBA and OneDrive SaveCopyAs","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:3144221"},"body":"I was assuming the file already is on a OneDrive folder. So ActiveWorkbook.Path would give you that.","body@stringLength":"100","rawBody":"I was assuming the file already is on a OneDrive folder. So ActiveWorkbook.Path would give you that.","author":{"__ref":"User:user:22322"},"isEscalated":null,"postTime":"2022-02-09T08:25:37.999-08:00","parent":{"__ref":"ForumReplyMessage:message:3143259"},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"Revision:revision:3719363_1":{"__typename":"Revision","id":"revision:3719363_1","lastEditTime":"2023-01-18T11:45:45.765-08:00"},"QueryVariables:ReplyList:message:3719363:1":{"__typename":"QueryVariables","id":"ReplyList:message:3719363:1","value":{"id":"message:3719363","sorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":false,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":false,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505309803":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1745505309803","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505309803":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1745505309803","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505309803":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1745505309803","value":{"accept":"Mark as Solution","accepted":"Marked as Solution","errorHeader":"Error!","errorAdd":"There was an error marking as solution.","errorRemove":"There was an error unmarking as solution.","solved":"Solved"},"localOverride":false},"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505309803":{"__typename":"CachedAsset","id":"text:en_US-components/messages/LinearReplyList-1745505309803","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"User:user:1741850":{"__typename":"User","id":"user:1741850","uid":1741850,"login":"Lio_Nel","biography":null,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2023-02-20T09:29:35.610-08:00"},"deleted":false,"email":"","avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-11.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"entityType":"USER","eventPath":"community:gxcuf89792/user:1741850"},"ForumReplyMessage:message:3722795":{"__typename":"ForumReplyMessage","uid":3722795,"id":"message:3722795","revisionNum":1,"author":{"__ref":"User:user:372045"},"readOnly":false,"depth":11,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:3719363"},"conversation":{"__ref":"Conversation:conversation:3124294"},"subject":"Re: Excel VBA and OneDrive SaveCopyAs","moderationData":{"__ref":"ModerationData:moderation_data:3722795"},"body":"
After a lot of head scratching I have found a way round the CopyAs issue. The workbooks attached contain macros stripped down fron the full coding in my main application. My AppBook.xlsm opens an \"auxiliary\" workbook AuxBook.xlsm which then SaveAs AppBook.xlsm to ArcBook.xlsm in (in my case) the Archive folder and returns to AppBook.xlsm which then runs the macro ClearData. I hope this may be relevant to your issue
Can't find means of attaching workbooks so the code is below:
Dim lcDiddlyFile, lcDiddlyPath, lcArchFile, lcArchPath As String Dim lvAns As Variant Application.DisplayAlerts = False Application.ScreenUpdating = False lcDiddlyPath = Application.ActiveWorkbook.Path & \"\\\" lcDiddlyFile = ActiveWorkbook.Name lcArchPath = lcDiddlyPath & \"Archive\\\" lcArchFile = \"ArcBook.xlsm\" Application.Workbooks(\"AppBook.xlsm\").SaveAs FileName:=(lcArchPath & lcArchFile) ActiveWorkbook.Save
End Sub 'Do Archive
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"203","kudosSumWeight":0,"postTime":"2023-01-23T07:18:53.624-08:00","lastPublishTime":"2023-01-23T07:18:53.624-08:00","metrics":{"__typename":"MessageMetrics","views":18702},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:3124294/message:3722795","repliesCount":1,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:3747711":{"__typename":"ModerationData","id":"moderation_data:3747711","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:3747711":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:1741850"},"id":"message:3747711","revisionNum":1,"uid":3747711,"depth":12,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:3722795"},"conversation":{"__ref":"Conversation:conversation:3124294"},"subject":"Re: Excel VBA and OneDrive SaveCopyAs","moderationData":{"__ref":"ModerationData:moderation_data:3747711"},"body":"Had the same problem I found this workaround. You simply need to relocate the URI file to local computer.
You have to rebuild the local path by replacing \"https://.../.../\" with local path. To do this you can get the OneDrive Path from the local environnement in vba with ENVIRON command => ENVIRON(\"OneDrive\") return the local path of OneDrive
Example here. Adapt it as you need 🙂
Public Function fRelocateOneDrivePath(strParamURIPath As String) As String
Dim strPath As String Dim I As Integer
' Replace all \"/\" by \"\\\" if exists strPath = Replace(strParamURIPath, \"/\", \"\\\")
' Return path with local OneDrivePath fRelocateOneDrivePath = Environ(\"OneDrive\") & \"\\\" & strPath
Else ' Do Nothing fRelocateOneDrivePath = strParamURIPath End If
End Function","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"203","kudosSumWeight":0,"postTime":"2023-02-20T09:46:36.752-08:00","lastPublishTime":"2023-02-20T09:46:36.752-08:00","metrics":{"__typename":"MessageMetrics","views":17582},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:3124294/message:3747711","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:3722795":{"__typename":"ModerationData","id":"moderation_data:3722795","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505309803":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1745505309803","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"excel-vba-and-onedrive-savecopyas","messageId":"3124294","replyId":"3719363"},"buildId":"HEhyUrv5OXNBIbfCLaOrw","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"openTelemetryConfigName":"o365","openTelemetryServiceVersion":"25.1.0","openTelemetryUniverse":"prod","openTelemetryCollector":"http://localhost:4318","openTelemetryRouteChangeAllowedTime":"5000","apolloDevToolsEnabled":false,"inboxMuteWipFeatureEnabled":false},"isFallback":false,"isExperimentalCompile":false,"dynamicIds":["./components/seo/QAPageSchema/QAPageSchema.tsx","./components/community/Navbar/NavbarWidget.tsx","./components/community/Breadcrumb/BreadcrumbWidget.tsx","./components/customComponent/CustomComponent/CustomComponent.tsx","./components/messages/TopicWithThreadedReplyListWidget/TopicWithThreadedReplyListWidget.tsx","./components/external/components/ExternalComponent.tsx","./components/messages/MessageView/MessageViewStandard/MessageViewStandard.tsx","../shared/client/components/common/List/UnstyledList/UnstyledList.tsx","./components/messages/MessageView/MessageView.tsx","../shared/client/components/common/List/UnwrappedList/UnwrappedList.tsx","./components/tags/TagView/TagView.tsx","./components/tags/TagView/TagViewChip/TagViewChip.tsx"],"appGip":true,"scriptLoader":[{"id":"analytics","src":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/pagescripts/1730819800000/analytics.js?page.id=ForumReplyPage&entity.id=board%3Aexcelgeneral&entity.id=message%3A3719363","strategy":"afterInteractive"}]}
