无直接链接或API时从企业内部网站下载CSV文件的方案求助

Hey there! Let's work through your problem step by step. Since you prioritized VBA first, then Python, I'll lead with those, plus a couple of low-code alternatives that fit your need for background execution.

1. VBA Solution (Excel/Access) #

This uses MSXML2.XMLHTTP to send authenticated requests in the background—no browser window required. The core fix here is replicating the session cookies and request headers your browser uses to access the CSV (since direct links fail due to missing authentication context).

Step 1: Grab Required Headers/Cookies #

First, use your browser's DevTools (F12 → Network tab) to:

• Navigate to the page where you normally initiate the CSV download
• Find the CSV request (look for .csv in the filename)
• Copy the critical Request Headers: Cookie, User-Agent, and Referer

Step 2: VBA Code Example #

Sub DownloadInternalCSV()
    Dim http As Object
    Dim csvUrl As String
    Dim savePath As String
    
    ' Update these values with your site details from DevTools
    csvUrl = "https://your-company-internal-site.com/path/to/target.csv"
    savePath = "C:\Your\Local\Save\Folder\output.csv"
    
    ' Initialize HTTP object
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    On Error GoTo Cleanup
    With http
        .Open "GET", csvUrl, False
        ' Add headers copied from DevTools
        .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
        .SetRequestHeader "Cookie", "YOUR_AUTH_COOKIE_STRING_HERE"
        .SetRequestHeader "Referer", "https://your-company-internal-site.com/page/where/you/download"
        .Send
        
        ' Verify request success
        If .Status = 200 Then
            ' Save response to CSV file
            Dim fso As Object
            Set fso = CreateObject("Scripting.FileSystemObject")
            Dim outputFile As Object
            Set outputFile = fso.CreateTextFile(savePath, True, True) ' Overwrite existing, UTF-8 encoding
            outputFile.Write .responseText
            outputFile.Close
            MsgBox "CSV downloaded successfully to: " & savePath
        Else
            MsgBox "Request failed. Status code: " & .Status & vbCrLf & .statusText
        End If
    End With

Cleanup:
    ' Clean up objects
    Set http = Nothing
    Set fso = Nothing
    Set outputFile = Nothing
End Sub

Notes: #

• Replace YOUR_AUTH_COOKIE_STRING_HERE with the exact cookie string from your browser's DevTools.
• If your site uses form-based login, extend this code to first send a POST request to the login endpoint to fetch a valid session cookie automatically.

2. Python Solution #

Python's requests library simplifies session management, making it easy to run background downloads without a GUI.

Step 1: Install Requests (if missing) #

pip install requests

Step 2: Python Code Example #

import requests

def download_internal_csv():
    # Configure these values with your site details
    csv_url = "https://your-company-internal-site.com/path/to/target.csv"
    save_path = "C:/Your/Local/Save/Folder/output.csv"
    
    # Headers copied from browser DevTools
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36",
        "Cookie": "YOUR_AUTH_COOKIE_STRING_HERE",
        "Referer": "https://your-company-internal-site.com/page/where/you/download"
    }
    
    # Use a session to persist authentication context
    with requests.Session() as session:
        # Optional: Uncomment to auto-login if needed (adjust login data/URL)
        # login_payload = {"username": "your_username", "password": "your_password"}
        # session.post("https://your-company-internal-site.com/login", data=login_payload)
        
        # Fetch the CSV
        response = session.get(csv_url, headers=headers)
        
        # Save if request succeeds
        if response.status_code == 200:
            with open(save_path, "wb") as f:
                f.write(response.content)
            print(f"CSV downloaded successfully to: {save_path}")
        else:
            print(f"Request failed. Status code: {response.status_code}, Message: {response.text}")

if __name__ == "__main__":
    download_internal_csv()

Notes: #

• For sites with SSO/MFA, use selenium in headless mode as a fallback (though it's slightly heavier than pure requests). Alternatively, manually refresh cookies when they expire.

3. Low-Code Alternatives #

If you prefer no code at all:

• Power Automate Desktop: Build a background flow using the "Launch Chrome in headless mode" action to navigate, authenticate, and download the CSV. Schedule it to run automatically via Windows Task Scheduler.
• curl Command: Run a curl command with your captured headers to download the CSV, then schedule it via Task Scheduler for background execution:

  curl "https://your-company-internal-site.com/path/to/target.csv" \
  -H "User-Agent: YOUR_USER_AGENT_STRING" \
  -H "Cookie: YOUR_AUTH_COOKIE_STRING" \
  -o "C:/Your/Local/Save/Folder/output.csv"
  

Quick Tips #

• Always test with a small request first to confirm your headers/cookies are valid.
• If cookies expire frequently, add logic to re-authenticate automatically in your code/flow.

内容的提问来源于stack exchange，提问作者Mr.Riply