Is it possible to use string variable as URL in excel VBA?

Question

As you can see below I have used fileName as string variable,which is used in the URL. When I run the code I get an error stating 'Run time error '-2147217376(80041020) The system cannot locate object specified.' Actually, I want to copy data from a .cg file to Excel. Any alternative method will also do. Please help.

'My Code

Sub ImportCG()

Dim directory As String, fileName As String

   directory = "D:\CG FILE"
  fileName = Dir(directory & "*.cg??")

ActiveWorkbook.Worksheets.Add
   ActiveWorkbook.XmlImport Url:= _
        "D:\CG FILE\filename" _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

     'Add CG_List
    Set NewSheet = Sheets.Add(Type:=xlWorksheet)
    ActiveSheet.Name = "CG_List"

    'Copy contents to Component Type List
    Columns("D:D").Select
    Selection.Copy
    Sheets("ComponentTypeList").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("CG_List").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ComponentTypeList").Select
    Columns("B:B").Select
    ActiveSheet.Paste
    Range("B15").Select

End Sub

h2so4 · Accepted Answer · 2017-03-27 12:21:59Z

2

your directory variable and url need some corrections

Sub ImportCG()

Dim directory As String, fileName As String

   directory = "D:\CG FILE\"
  fileName = Dir(directory & "*.cg??")

ActiveWorkbook.Worksheets.Add
   ActiveWorkbook.XmlImport Url:= _
        directory & filename _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

     'Add CG_List
    Set NewSheet = Sheets.Add(Type:=xlWorksheet)
    ActiveSheet.Name = "CG_List"

    'Copy contents to Component Type List
    Columns("D:D").Select
    Selection.Copy
    Sheets("ComponentTypeList").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("CG_List").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ComponentTypeList").Select
    Columns("B:B").Select
    ActiveSheet.Paste
    Range("B15").Select

End Sub

answered Mar 27, 2017 at 12:21

h2so4

1,5771 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chaitanya Kamble Over a year ago

Hello Buddy thanks for the solution ,its working as wanted it.

Nathan_Sav · Accepted Answer · 2017-03-27 12:15:23Z

1

"D:\CG FILE\" & filename you need to concatenate the 2

answered Mar 27, 2017 at 12:15

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

1 Comment

Chaitanya Kamble Over a year ago

Hello Buddy thanks for the reply ,but the concatenation is done at this step fileName = Dir(directory & "*.cg??")

Collectives™ on Stack Overflow

Is it possible to use string variable as URL in excel VBA?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related