Home Run Time Error 5: Invalid Procedure Call or Argument
Reply: 2

Run Time Error 5: Invalid Procedure Call or Argument

mitchmitch24
1#
mitchmitch24 Published in 2018-02-14 14:45:18Z

I am getting the

Run Time Error 5: Invalid Procedure Call or Argument

on this line:

With ActiveSheet.QueryTables.Add(Connection:=User_File_Path, Destination:=Range("$A$1"))

I have a message box right before that line that tells me the User_File_Path, which is populating, and when I hover over the Connection:=User_File_Path it does display the file path as well... not sure how to fix this. Any help is appreciated!!

Vityata
2#
Vityata Reply to 2018-02-14 15:10:40Z

Try like this:

With ActiveSheet
      Dim someTable As QueryTable
      Set someTable = .QueryTables.Add(Connection:=User_File_Path, _
                                          Destination:=Range("$A$1"))
End With

Here is the MSDN article for the QueryTables.Add() method:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/querytables-add-method-excel

This works, if your connection is to a local csv file:

Public Sub TestMe()   

    Dim someTable As QueryTable
    Set someTable = ActiveSheet.QueryTables.Add(Connection:= _
                    "TEXT;C:\MyText.csv", _
                    Destination:=Range("$A$1"))

    someTable.Refresh
End Sub
Darren Bartrup-Cook
3#
Darren Bartrup-Cook Reply to 2018-02-14 15:11:46Z

SOLUTION:

"Text;" & User_File_Path & User_File_Name

i.e.

With ActiveSheet.QueryTables.Add(Connection:="Text;" & User_File_Path & User_File_Name, Destination:=Range("$A$1"))
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305774 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO