Início / coding / Perguntas / 79565060
Accepted
Eriknme
Asked: 2025-04-10 02:18:27 +0800 CST

Tentando abrir arquivo de texto via SharePoint com VBA

  • 772

Estou tentando abrir um arquivo de texto via Sharepoint para ler, mas continuo recebendo uma mensagem de erro (Tempo de execução 52 Nome ou número inválido).

Recebo o erro de depuração na linha strFile: Abrir strFile para entrada como #1

Dim FileToOpen as Variant
Dim sh as Worksheet
Dim strFile As String
    
    '-------Open Text file -------------
Set FileToOpen = Application.FileDialog(msoFileDialogFilePicker)

With FileToOpen
        .Filters.Clear
        .Filters.Add "Text Files", "*.tx*"
        .Title = "Browse to text File"
        .AllowMultiSelect = False
        .InitialFileName = "https:\\my.sharepoint.com\sites\THALI01\"
        If .Show = True Then
            strFile = .SelectedItems(1)
        End If
    End With

   '''-----reading lines on text file-----

 Open strFile For Input As #1
    Do Until Left(Oneline, 42) = "Mapping Definition Name: "
      Line Input #1, Oneline
      DoEvents
    Loop
    
        Acq = Trim(Mid(Oneline, 42, 30))
        sh.Range("B8") = Acq
 
End Sub
excel

1 respostas

  1. Best Answer

    Não é possível chamar Openum arquivo usando um caminho HTTP: isso é válido apenas para arquivos locais/de rede. Você precisará baixar o arquivo e abrir a cópia local.

    Por exemplo, usando o URLDownloadToFilemétodo da API do Windows:

    'API declarations
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, _
      ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
      ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub ChooseAndOpenFileFromSharePoint()
    Dim FileToOpen As Variant, tempFile As String
    Dim sh As Worksheet
    Dim strFile As String
        
    '-------Open Text file -------------
    Set FileToOpen = Application.FileDialog(msoFileDialogFilePicker)
    
    With FileToOpen
        .Filters.Clear
        .Filters.Add "Text Files", "*.tx*"
        .Title = "Browse to text File"
        .AllowMultiSelect = False
        .InitialFileName = "https://theravance.sharepoint.com/sites/RIR/TestLibrary/"
        If .Show = True Then
            strFile = .SelectedItems(1)
            tempFile = TempPath()
            DownloadFile strFile, tempFile
            Debug.Print "Downloaded:" & vbLf & "    " & strFile & _
                         vbLf & "to" & vbLf & "     " & tempFile
        Else
            Exit Sub
        End If
    End With
    
    'process file at `tempFile`....
    
 
End Sub

'return a full path to a file in the user's Temp folder
Function TempPath() As String
    With CreateObject("scripting.filesystemobject")
        TempPath = .BuildPath(.GetSpecialFolder(2), .GetTempName)
    End With
End Function

Function DownloadFile(sURL, sSaveAs) As Boolean
    DownloadFile = (URLDownloadToFile(0, sURL, sSaveAs, 0, 0) = 0)
End Function
    • 1

relate perguntas