从60个URL中获取CSV数据，并使用VBA一个接一个地附加

Question

我有60个URL，每个URL都提供一个CSV文件，数据每天都是新的。我想完成的是创建一个VBA脚本，该脚本将使用所有60个URL，并在excel的同一工作表中获取数据并一个接一个地附加1。之后我想每天刷新它？

我已经开始录制宏了，但它并没有给我一个循环。感谢您的帮助，我是VBA的新手。

Answer 1

我会按如下方式进行：

• 准备对象

  Dim wbTmp as Workbook
  Dim myWb as workbook
  Dim myRng as Range
  Dim rngTmp as Range
  Set myWb = ActiveWorkbook
  myWb.Worksheets(1).UsedRange.Clear
  Set myRng = myWb.Worksheets(1).Cells(1,1) 
  

• 将URL保存在阵列中

  Dim myURLs(1 To 60) as Variant
  myURLs(1) = "someurl"
  ...
  myURLs(60) = "lasturl"
  

• 循环通过阵列

  Dim myURL as Variant
  For Each myURL in myURLs
      ... 'next point fills the loop
  

• 打开每个CSV作为工作本（一个接一个）

      Set wbTmp = Workbooks.Open(Filename:=myURL)
      'OK, thats not for sure - I dont know how to open files from internet :)
      'Thats your part
  

• 将使用范围复制到主工作簿

      Set rngTmp = wbTmp.Worksheets(1).UsedRange
      Call rngTmp.Copy(Destination:=myRng)
      Set myRng = myRng.Offset(rngTmp.Rows.Count)
      Call wbTmp.Close(savechanges:=False)
  

• 结束循环，杀死目标

  Next myURL
  Set wbTmp = Nothing
  Set myWb = Nothing
  Set myRng = Nothing
  Set rngTmp = Nothing