CODEWITHSUNDEEP
vbaexcel
sharp
sharp

Reputation: 2158

Run-time error '52' Bad file name or number

I am getting the run-time error '52': for the code below. The VBA highlights error: vFile = Dir(ThisWorkbook.Path & "C:\Users\user_id\Desktop\ml\testdirectory\").

This directory exists and there are .csv files in here. Here are the file names in this directory: 12345678-111111.forecast.csv; 12345-222.forecast.csv. 

'fixing:run time error '-2147467259 automation error unspecified error

Sub Unprotect_WorkSheet_With_Password()
Sheets("Sheet1").Unprotect "YourPassword"
End Sub



    Sub Consolidate()

    Dim sSQL        As String       'SQL String
    Dim oCn         As Object       'Connection
    Dim oRs         As Object       'Recordset
    Dim vFile       As Variant      'File Name
    Dim sCustomer   As String       'Customer ID
    Dim sItem       As String       'Inventory Item ID

'   Get filenames
    vFile = Dir(ThisWorkbook.Path & "C:\Users\user_id\Desktop\ml\testdirectory\*.csv")

'   Create SQL
    While vFile <> vbNullString
        If sSQL <> vbNullString Then sSQL = sSQL & vbCr & "Union " & vbCr
        sCustomer = Split(vFile, "-")(0)
        sItem = Split(Split(vFile, "-")(1), ".")(0)
        sSQL = sSQL & "Select '" & sCustomer & "' as Customer, '" & sItem & "' as Item, * from [" & vFile & "]"
        vFile = Dir
        DoEvents
    Wend
'   Create Connection Objects
    Set oCn = CreateObject("ADODB.Connection")
    Set oRs = CreateObject("ADODB.Recordset")

    oCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & ThisWorkbook.Path & ";" & _
            "Extended Properties=""Text;HDR=YES;FMT = CSVDelimited"";"
    oRs.Open sSQL, oCn
    Debug.Print sSQL

    If Sheet1.ListObjects.Count > 0 Then Sheet1.ListObjects(1).Delete
    Sheet1.ListObjects.Add( _
        SourceType:=xlSrcQuery, _
        Source:=oRs, _
        Destination:=Sheet1.Range("C6")).QueryTable.Refresh

    oRs.Close
oCn.Close

    Set oRs = Nothing
    Set oCn = Nothing

End Sub

After research on the internet, it showed it may have been caused by .forecast.csv. I removed .forecast and ran the VBA code, but it didn't solve the problem.

Another test: (I get different errors) Path = "C:\Users\user_id\Desktop\ml\testdirectory\" vFile = Dir(Path & "*.csv")

Path = "C:\Users\user_id\Desktop\ml\testdirectory\"
vFile = Dir(Path & "*.forecast.csv")

Upvotes: 1

Views: 11976

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Your ThisWorkbook.Path will return something like C:\Documents and Settings\myprofile\Desktop.

So your DIR line reads as: "C:\Documents and Settings\myprofile\DesktopC:\Users\user_id\Desktop\ml\testdirectory\*.csv".

Surely that can't be correct - you've got the drive letter in there twice....

Maybe it should be something like ThisWorkbook.Path & "\ml\testdirectory\*.csv" providing ml is in a subfolder in the same folder that your workbook is in.

Upvotes: 1

Related Questions