Code to open csv file using ws.QueryTables.Add failing

[netrixuser]

Good evening,
I have some code given to me to save a zipped email attachment to disk, unzip the file and then open the csv file. The code worked once but now throws an error 1004 at the .refresh command.
I could probably use my limited knowledge to use the Get Data approach and then manipulate the data but I would like to try and understand the method below a little more.

The code to get the email attachment saved as a .csv file onto a 0365 Share Point location works fine. The next lines of code are:

        'Open file into workbook
        Dim oFSO As Object
        Dim oFolder As Object
        Dim oFile As Object
        
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder(AttachmentDestinationFolder) 'this line resolves to the location of the csv file (I have the SP location synced to my OneDrive)
        
        Dim FileName As String
        For Each oFile In oFolder.Files
            FileName = oFile.Path
            Exit For
        Next oFile
        
        Dim Ws As Worksheet
    
        Set Ws = ActiveWorkbook.Sheets("Scratch Sheet")            'I am running the code from within the active workbook

        With Ws.QueryTables.Add(Connection:="TEXT;" & FileName, _
                    Destination:=Ws.Range("A1"))
             .TextFileParseType = xlDelimited
             .TextFileCommaDelimiter = True
             .Refresh                                             'The code throws an error 1004 here - see uploaded screenshot
        End With

For the first run of the code, the data from the csv file was populated in the Scratch Sheet tab. Before running the code again (with the same attachment) I deleted the csv file from the attachment destination folder and deleted the data from the Scratch Sheet tab - I did get a pop-up warning when deleting the data - but didn't take note as I just expected the data to be re entered when the code ran for the second time.

Any help very much appreciated.

Kind regards

Netrix

 

[Domenic]

Try running your code again. This time, though, when the error occurs and you click on Debug, check to see whether a path and file name has been assigned to your variable FileName. You can do so by either moving your cursor over the variable or typing the following line of code in the Immediate Window (Ctrl+G), and pressing the ENTER key...

? FileName

Has your variable FileName in fact been assigned a path and filename?

 

[netrixuser]

Try running your code again. This time, though, when the error occurs and you click on Debug, check to see whether a path and file name has been assigned to your variable FileName. You can do so by either moving your cursor over the variable or typing the following line of code in the Immediate Window (Ctrl+G), and pressing the ENTER key...

? FileName

Has your variable FileName in fact been assigned a path and filename?

Hi Domenic, thank you for your reply.

In answer to your question, yes - the FileName resolves to the path and filename of the Workbook that I want the csv file to open in [also the workbook I am running the code from]

I think I need to revisit the code in general. When up and running there could be two or more emails with attachments in the Outlook folder, the attachments would have similar names but with a date and time appended, like xxxxxx 20230706033038.csv

I only have one example in Outlook with the attachment at present. The code below is pointing to the Workbook that I am running the code from, and there will only be one instance of this workbook.
I think it should be pointing to the filenames of the attachments

As you can tell, a bit of a newbie here !

        Dim FileName As String              'resolves to C:\aaa\bbb\ccc.xlsm
        For Each oFile In oFolder.Files     'oFile is C:\aaa\bbb\ccc.xlsm     oFolder is C:\aaa\bbb
            FileName = oFile.Path
            Exit For
        Next oFile

 

[Domenic]

In that case, as you're looping through each file within the specified folder, check whether the file is a CSV file...

        Dim FileName As String
        For Each oFile In oFolder.Files
            If LCase(Right(oFile.Name, 4)) = ".csv" Then
                FileName = oFile.Path
                Exit For
            End If
        Next oFile
        
        If Len(FileName) = 0 Then
            MsgBox "No CSV file found!", vbExclamation
            Exit Sub
        End If

Does this help?

 

[netrixuser]

In that case, as you're looping through each file within the specified folder, check whether the file is a CSV file...

        Dim FileName As String
        For Each oFile In oFolder.Files
            If LCase(Right(oFile.Name, 4)) = ".csv" Then
                FileName = oFile.Path
                Exit For
            End If
        Next oFile
       
        If Len(FileName) = 0 Then
            MsgBox "No CSV file found!", vbExclamation
            Exit Sub
        End If

Does this help?

It does ! thank you so much. Apologies for the delay in replying - I had a week of Man-Flu

 

[Domenic]

That's great, glad I could help.

Cheers!