1

I need to create a text file with the name of the active workbook, but with the .txt extension. I use the following code:

Public Sub textfile()
Dim fso As Variant, ts As Variant
Dim tempFileName As String
   Set fso = CreateObject("Scripting.FileSystemObject")
   tempFileName = ActiveWorkbook.FullName + ".txt"
   Debug.Print tempFileName
   Set ts = fso.CreateTextFile(tempFileName)
   ts.WriteLine "Hello"
   ts.Close
End Sub

With local file, it works perfectly. But if the file is on OneDrive, it gives error 52 "bad file name or number". The path to the file is:

xxx-my.sharepoint.com/personal/yyy_xxx_onmicrosoft_com/Documents/test.xlsx.txt

Any ideas on how to solve this?

Dejan Ristanovic
  • 49
  • 7
  • As long as the excel file is synced locally by OneDrive, you can use [this solution](https://stackoverflow.com/a/73577057/12287457) to convert the return value of `ActiveWorkbook.FullName` to a local path. That way, the filesystem object will create the file locally and OneDrive will do the synchronization and upload it automatically. By the way, I would highly recommend using `ThisWorkbook.FullName` instead of `ActiveWorkbook.FullName`. – GWD Oct 22 '22 at 23:52

1 Answers1

2

The filesystem object is way older than onedrive and it does not understand its paths. However if you have the onedrive folder synced to your local filesystem, eg: c:\users\name\onedrive... you can use this local path instead, and it will be synced back to onedrive.

There are other ways to upload files to onedrive using the onedrive api. Those are however much more complex and require a bit of authentication knowledge (https://learn.microsoft.com/en-us/onedrive/developer/rest-api/getting-started/authentication?view=odsp-graph-online). If you are up to it, have a look here: https://learn.microsoft.com/en-us/onedrive/developer/rest-api/api/driveitem_put_content?view=odsp-graph-online.

Mirronelli
  • 740
  • 5
  • 14
  • Thank you for your comment. In the meanwhile I found that the command like ActiveWorkbook.SaveAs Filename:= _ tempFileName, FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False can access OneDrive using those xxx-my.sharepoint.com paths. But it creates XLSX file. A pity they did not provide similar functionality to CreateTextFile. – Dejan Ristanovic Mar 14 '21 at 01:09