[RESOLVED] Save excel in OneDrive

[newbie2]

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

[Arnoutdv]

Check this thread:
https://www.vbforums.com/showthread....e-example-code

[newbie2]

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

[fafalone]

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

[VanGoghGaming]

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

[fafalone]

...

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%")

[newbie2]

thank you for your interest

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

I'm getting permission denied

[newbie2]

...

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

[Arnoutdv]

I think it's the TwinBasic declaration.
This adapted version works for me:

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

[fafalone]

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

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.

[newbie2]

thank you gentelmen
solved

[newbie2]

thank you gentlemen
solved

[newbie2]

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

[Arnoutdv]

Using the VB6 FileCopy command I was able to copy a file.
But you have to specify the complete source and destination paths
Like

FileCopy "C:\Temp\MyTest.docx", "C:\Users\newbie2\OneDrive\MyTest.docx"