Consulting

Results 1 to 4 of 4

Thread: VBA import from csv - copy into existing cells rather than insert new

  1. #1

    VBA import from csv - copy into existing cells rather than insert new

    Hi Guys,
    I have a code that pulls through a csv file and imports the data into a sheet named "test".
    what I want it to do is copy the data into the exisitng cells rather than adding new cells witht the data in.

        Dim URL As String    Dim destCell As Range
        
        URL = "https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1426550400&period2=1584403200&interval=1d&events=history"
        
        Set destCell = Worksheets("test").Range("A1")
        
        With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=destCell)
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        
        destCell.Parent.QueryTables(1).Delete
    
    
    End Sub

    My goal with this is so i can have a set of 5 or 6 tables of data on a single sheet that will update depending on user search box.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    try adding the red line:
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=destCell)
      .TextFileStartRow = 1
      .TextFileParseType = xlDelimited
      .TextFileCommaDelimiter = True
      .RefreshStyle = xlOverwriteCells
      .Refresh BackgroundQuery:=False
    End With
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      URL = "TEXT;https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1426550400&period2=1584403200&interval=1d&events=history" 
    
      with sheets("test")
         if .Querytables.count=0 Then .QueryTables.Add URL, .cells(1)
        .Querytables(1).refresh False 
      end with
    End Sub

  4. #4
    Perfect thanks guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •