Results 1 to 14 of 14

Thread: [RESOLVED] Save excel in OneDrive

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Resolved [RESOLVED] Save excel in OneDrive

    Hello Vbforums
    I'm exporting a database table data to excel
    I need to save that newly created excel file in OneDrive
    Is this possible?
    thank you

  2. #2

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Re: Save excel in OneDrive

    Sorry but I couldn't figure out how to do that
    can you provide an example please?

  4. #4
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    6,093

    Re: Save excel in OneDrive

    Dim sPathToOneDrive As String
    sPathToOneDrive = Environ("OneDrive")

  5. #5
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,740

    Wink Re: Save excel in OneDrive

    Since "OneDrive" is installed by default in your user folder, "Environ" will definitely break for user names containing Unicode characters!

  6. #6
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    6,093

    Re: Save excel in OneDrive

    ...

    Code:
    Public Declare Function ExpandEnvironmentStringsW Lib "kernel32" (ByVal lpSrc As LongPtr, ByVal lpDst As LongPtr, ByVal nSize As Long) As Long
    Public Const MAX_PATH                  As Long = 260
    
        Private Function ExpandEnvVars(sIn As String) As String
        'Expand environment variables
        Dim sTmp As String
        Dim chs As Long
        
        sTmp = String$(MAX_PATH, 0)
        chs = ExpandEnvironmentStringsW(StrPtr(sIn), StrPtr(sTmp), MAX_PATH)
        If chs > 1& Then
            ExpandEnvVars = Left$(sTmp, chs - 1&) 'It includes a null terminator
        Else
            ExpandEnvVars = sIn
        End If
        
        End Function
    Dim sPathToOneDrive As String
    sPathToOneDrive = ExpandEnvVars("%OneDrive%")

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Re: Save excel in OneDrive

    thank you for your interest
    Code:
    Dim sPathToOneDrive As String
    sPathToOneDrive = Environ("OneDrive")
    I'm getting permission denied

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Re: Save excel in OneDrive

    Quote Originally Posted by fafalone View Post
    ...

    Code:
    Public Declare Function ExpandEnvironmentStringsW Lib "kernel32" (ByVal lpSrc As LongPtr, ByVal lpDst As LongPtr, ByVal nSize As Long) As Long
    Public Const MAX_PATH                  As Long = 260
    
        Private Function ExpandEnvVars(sIn As String) As String
        'Expand environment variables
        Dim sTmp As String
        Dim chs As Long
        
        sTmp = String$(MAX_PATH, 0)
        chs = ExpandEnvironmentStringsW(StrPtr(sIn), StrPtr(sTmp), MAX_PATH)
        If chs > 1& Then
            ExpandEnvVars = Left$(sTmp, chs - 1&) 'It includes a null terminator
        Else
            ExpandEnvVars = sIn
        End If
        
        End Function
    Dim sPathToOneDrive As String
    sPathToOneDrive = ExpandEnvVars("%OneDrive%")
    I'm getting user defined type not defined

  9. #9
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    6,091

    Re: Save excel in OneDrive

    I think it's the TwinBasic declaration.
    This adapted version works for me:
    Code:
    Option Explicit
    
    Private Declare Function ExpandEnvironmentStringsW Lib "kernel32" (ByVal lpSrc As Long, ByVal lpDst As Long, ByVal nSize As Long) As Long
    Private Const MAX_PATH                  As Long = 260
    
    Public Function ExpandEnvVars(sIn As String) As String
      'Expand environment variables
      Dim sTmp As String
      Dim chs As Long
      
      sTmp = String$(MAX_PATH, 0)
      chs = ExpandEnvironmentStringsW(StrPtr(sIn), StrPtr(sTmp), MAX_PATH)
      If chs > 1& Then
          ExpandEnvVars = Left$(sTmp, chs - 1&) 'It includes a null terminator
      Else
          ExpandEnvVars = sIn
      End If
        
    End Function

  10. #10
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    6,093

    Re: Save excel in OneDrive

    I use LongPtr in all my VB6 code now too. I even removed the PtrSafe so it was VB6-compatible instead of VBA7 or tB. oleexp.tlb defines LongPtr, or you can add an enum

    Code:
    Public Enum LongPtr
        [_]
    End Enum
    Even if you're not using tB right now, it makes sense to future proof with the bonus of easier VBAx64 compatibility. I know firsthand how nice it is to not need to preserve the 64bit type information instead of having to find it from scratch all over again.

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Re: Save excel in OneDrive

    thank you gentelmen
    solved

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Re: Save excel in OneDrive

    thank you gentlemen
    solved

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    943

    Re: [RESOLVED] Save excel in OneDrive

    I'm sorry to re-open this thread again

    thanks to your help I can save my files on OneDrive but I get permission denied when I try to export a file to OneDrive by means of FSO.CopyFile
    I should note that I can manually copy a file using Windows explorer
    thank you for any help
    Last edited by newbie2; Jun 19th, 2024 at 12:47 PM.

  14. #14
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    6,091

    Re: [RESOLVED] Save excel in OneDrive

    Using the VB6 FileCopy command I was able to copy a file.
    But you have to specify the complete source and destination paths
    Like
    Code:
    FileCopy "C:\Temp\MyTest.docx", "C:\Users\newbie2\OneDrive\MyTest.docx"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width