Not understanding syntax of fso

[KasperC]

Hello,

I'm trying to refer to a textfile and set that as an array, but i'm met with an error when trying to refer to ThisWorkbook.Path (Bad file name or number)
And I don't understand how to fix it.

I'm using the same type of argument to open/refer to a Workbook, and this works just fine.

I'm currently synced to a OneDrive, and the location of the script will both be in OneDrive clouds and locally saved - so trimming/setting a constant FileName isnt an option.
The debug.print FileName is as follows:

https://company-my.sharepoint.com/personal/name_company_no/folder1/folder2/folder_of_document_running_the_script\folderx\foldery\document.txt

Sub test_array()
    Dim fso As Object, MyFile As Object
    Dim FileName As String, Arr As Variant

                       
    FileName = ThisWorkbook.Path & "\folderx\foldery\document.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set MyFile = fso.OpenTextFile(FileName, 1)                                                                       'This line gives the error.
    Arr = Split(MyFile.ReadAll, vbNewLine)
   
    Workbooks.Open FileName:=ThisWorkbook.Path & "\folderx\exceldocument.xlsx"       'This line works just fine, and opens the correct document.
   
    Debug.Print FileName
End Sub

Currently my only solution is setting a "default" FileName to:
"C:\Users\User\OneDrive - Company\folder1\folder_of_document_running_the_script\folderx\foldery\document.txt"
But i'd like for this to be dynamic.

Any clues / tips? - Am I misunderstanding something fundamentally of this method?

Much appreciated

 

[DanteAmor]

Hi @KasperC

"An image says more than a thousand words"
"Una imagen dice más que mil palabras"
- Dicho popular -​

 

[KasperC]

Hi @KasperC


View attachment 94376

"An image says more than a thousand words"
"Una imagen dice más que mil palabras"
- Dicho popular -​

Hi, and thank you for your reply.

The text document is in a subfolder (foldery) that lies in folderx, they are not in the same folders per se.

What I ment is that I thought i applied the same logic, but that it does not work for the fso.. I copied the filepaths from the fileexplorer to be sure.

Any other ideas as of why I'm getting this error?

 

[DanteAmor]

The text document is in a subfolder (foldery) that lies in folderx, they are not in the same folders per se.
Any other ideas as of why I'm getting this error?

You can show in an image the explorer of windows to verify that the file "document.txt" is in the folder that you mention.

For example:

Below you can see the execution of the code and how the 'arr' variable holds the content of the file:

The file document.txt:

(Bad file name or number)

The problem is in the name of your folder or in the name of the file.

 

[RoryA]

I suspect fso doesn't like sharepoint addresses.

 

[DanteAmor]

Here another way to open a txt file:

Sub test_array_v2()
  Dim FileName As String, Arr As Variant
                    
  FileName = ThisWorkbook.Path & "\folderx\foldery\document.txt"
 
  Open FileName For Input As #1
    Arr = Split(Input(LOF(1), #1), Chr(10))
  Close #1
End Sub

-------------------​

Here another way to open a txt file:

Sub test_array_v3()
  Dim FileName As String, Arr As Variant
                     
  FileName = ThisWorkbook.Path & "\folderx\foldery\document.txt"

  Workbooks.OpenText FileName:=FileName, Origin:=xlMSDOS, _
      StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
      ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, Other:=False
      
  Arr = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Rows.Count).End(3)).Value
End Sub

 

[KasperC]

I suspect fso doesn't like sharepoint addresses.

I do also suspect now that this is the issiue. I moved the files to a local adress, aswell as a shared disk and everything ran smoothly. Wonder if there is a way around this - guess I'll have to research a bit or elevate the issiue to Microsoft.

Here another way to open a txt file:

View attachment 94380

Sub test_array_v2()
  Dim FileName As String, Arr As Variant
                   
  FileName = ThisWorkbook.Path & "\folderx\foldery\document.txt"
 
  Open FileName For Input As #1
    Arr = Split(Input(LOF(1), #1), Chr(10))
  Close #1
End Sub

-------------------​

Here another way to open a txt file:
View attachment 94381

Sub test_array_v3()
  Dim FileName As String, Arr As Variant
                    
  FileName = ThisWorkbook.Path & "\folderx\foldery\document.txt"

  Workbooks.OpenText FileName:=FileName, Origin:=xlMSDOS, _
      StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
      ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, Other:=False
     
  Arr = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Rows.Count).End(3)).Value
End Sub

Thank you for these - the first method here returned the same error through sharepoint, but worked fine locally/shared disk.

Seccond one worked on sharepoint - so this might solve my issiues when working through sharepoint. Thank you so much!