VBA检查文件（来自网站）是否存在 [英] VBA Check if file (from website) exists

问题描述

我正在尝试通过使用VBA的网站打开一个excel文件。文件的地址（路径）从一个月到每月更改。例如：

• 7月份，文件名为：
  返回一个表示名称的字符串与指定的模式或文件属性匹配的文件，目录或文件夹，或驱动器的卷标。

  
  
  

  您需要的是以下函数来检查URL是否有效，
  PS将功能放在模块

   函数URLExists（url As String）As Boolean 
   Dim Request As Object 
   Dim ff As Integer 
   Dim rc As Variant 
   
   On Error GoTo EndNow 
   Set Request = CreateObject（WinHttp.WinHttpRequest.5.1）
   
  请求
  。打开GET，url，False 
   .Send 
   rc = .StatusText 
  结束
   Set Request = Nothing 
  如果rc =OK，则URLExists = True 
   
  退出函数
   EndNow：
  结束函数
    

  然后使用宏中的函数

   如果URLExists（DirFile ）= 0然后
  设置wbA = Workbooks.Open（http://www.clevelandfed.org/research/data/inflation_expectations/& Format（Now，YYYY）&/&格式（DateAdd（m，-1，Date），MMMM）&/excel1.xls，IgnoreReadOnlyRecommended：= True）
   wbA.Activate 
  '如果当前月份文件存在， 打开它 
   Else 
  设置wbA = Workbooks.Open（DirFile，IgnoreReadOnlyRecommended：= True）
  如果
    

  
  

  Please bear with me as I am a beginner at VBA.

  I am trying to open an excel file through a website using VBA. The address (path) of the file changes from month to month. For example:

  • In July, the file name is: http://www.clevelandfed.org/research/data/inflation_expectations/2014/July/excel1.xls

  • In August, the file name is: http://www.clevelandfed.org/research/data/inflation_expectations/2014/August/excel1.xls

  The problem is that I never know in advance when the new file for the month is going to be published. Therefore, I need to check in the VBA code if the current month file exist, if not, I would just open the previous month file.

  This is what I have tried:

  Dim DirFile As String
  Dim wbA As Workbook
  
  DirFile = "http://www.clevelandfed.org/research/data/inflation_expectations/" & Format(Now, "YYYY") & "/" & Format(Now, "MMMM") & "/excel1.xls"
  
  ' Check if the file for current month does not exist, open previous month's file
  If Len(Dir(DirFile)) = 0 Then
      Set wbA = Workbooks.Open("http://www.clevelandfed.org/research/data/inflation_expectations/" & Format(Now, "YYYY") & "/" & Format(DateAdd("m", -1, Date), "MMMM") & "/excel1.xls", IgnoreReadOnlyRecommended:=True)
  
  'If the current month file exists, open it
  Else
      Set wbA = Workbooks.Open(DirFile, IgnoreReadOnlyRecommended:=True)
  End If
  

  However, this results in an error:

  I am assuming this is due to the fact that this is a file that resides on a website. Could anyone please help resolve this issue?

  Thank you!

  解决方案

  You are correct in assuming Dir() doesn't work for files residing on Websites

  Dir Function Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

  What you need is the following function to check if the URL is valid, P.S. Place the function in Module

  Function URLExists(url As String) As Boolean
      Dim Request As Object
      Dim ff As Integer
      Dim rc As Variant
  
      On Error GoTo EndNow
      Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
  
      With Request
        .Open "GET", url, False
        .Send
        rc = .StatusText
      End With
      Set Request = Nothing
      If rc = "OK" Then URLExists = True
  
      Exit Function
  EndNow:
  End Function
  

  Then use the function in your Macro

  If URLExists(DirFile) = 0 Then
      Set wbA = Workbooks.Open("http://www.clevelandfed.org/research/data/inflation_expectations/" & Format(Now, "YYYY") & "/" & Format(DateAdd("m", -1, Date), "MMMM") & "/excel1.xls", IgnoreReadOnlyRecommended:=True)
      wbA.Activate
  'If the current month file exists, open it
  Else
      Set wbA = Workbooks.Open(DirFile, IgnoreReadOnlyRecommended:=True)
  End If
  

  这篇关于VBA检查文件（来自网站）是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！