CODEWITHSUNDEEP
vbaexcel
Bosco Tsin
Bosco Tsin

Reputation: 183

Excel VBA - Seems Something Wrong with ws Naming

I am so desperate I keep running into VBA error 1004 for the below code:

  Sub GetRawPrices()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim wq As QueryTable
  Dim url As String
  Dim wb0 As Workbook
  Set wb0 = ThisWorkbook
  Dim i As Integer
  For i = 2 To wb0.Worksheets("Consolidation").Range("A1").End(xlDown).Row Step 1
  Set wb = Workbooks.Add
  wb.SaveAs Filename:="G:\2015July\Desktop2\HORIZONTAL\" & wb0.Worksheets("Consolidation").Cells(i, 1).Value & ".xlsx"
  url = _
    "TEXT;" & _
    "http://ichart.finance.yahoo.com/table.csv?" & _
    "s=" & _
    wb0.Worksheets("Consolidation").Cells(i, 1).Value & _
    "&" & _
    "a=01&" & _
    "b=01&" & _
    "c=2015&" & _
    "d=03&" & _
    "e=12&" & _
    "f=2017&" & _
    "g=d&" & _
    "ignore=.csv"
  Set ws = wb.Worksheets.Add(After:=Sheet3)
  ws.Name = "YAHOO_DATA"
  Set wq = ws.QueryTables.Add( _
    Connection:=url, _
    Destination:=ws.Range("A1"))
  wq.TextFileParseType = xlDelimited
  wq.TextFileCommaDelimiter = True
  wq.Refresh BackgroundQuery:=False
  wb.Close savechanges:=True
  Next i

End Sub

It looks like to me there is something wrong with the two lines below, because I can create and save the wb properly, and I only run into error 1004 without creating the new worksheet ws "YAHOO_DATA".

  Set ws = wb.Worksheets.Add(After:=Sheet3)
  ws.Name = "YAHOO_DATA"

But what can be wrong? I have another Excel file that runs the almost exact same code and it works well...

I would be grateful if anyone can solve this problem for me... It's been 2 hours since I have stared at this code already! Thanks!

Bosco

Upvotes: 0

Views: 77

Answers (1)

Rowen Chumacera
Rowen Chumacera

Reputation: 529

Change the following line

Set ws = wb.Worksheets.Add(After:=Sheet3)

to

Set ws = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))

In some MS Excel versions, new workbooks don't have 3 new sheets created.

Upvotes: 3

Related Questions