自动将数据从Excel工作表保存到CSV文件的代码 [英] Code to automatically save data from Excel sheet to CSV file

问题描述

是否存在代码，例如每小时将工作表1中A1:B10中的数据保存到CSV文件中?

Does there exist a code to for example save every hour the data from A1:B10 in sheet1 to a CSV file ?

推荐答案

尽管可以通过许多不同的方式来完成，但这是我要做的:

Although this can be done in many different ways, this is what I'd do:

1)将以下子项添加到新文件中.我们称之为"auto.xlsb"

1) Add the following sub to a new file. Let's call it "auto.xlsb"

Sub SaveRangeToCSV()

    Dim rng As Range
    Dim originWB As Workbook
    Dim originWS As Worksheet
    Dim newBook As Workbook
    Dim newBookWS As Worksheet

    'Open the file you want to copy the range from
    Set originWB = Workbooks.Open("path_to_file_that_contains_the_range_you_want_to_copy.xlsx")
    Set originWS = ThisWorkbook.Sheets("name_of_the_sheet_where_the_range_is")
    Set rng = originWS.Range("A1:B10")

    'Add new workbook (csv file)
    Workbooks.Add
    Set newBook = ActiveWorkbook
    Set newBookWS = newBook.Sheets(1)

    'Copy range from origin to destination (csv file)
    rng.Copy Destination:=newBookWS.Range("A1")

    'Save csv file
    newBook.SaveAs Filename:=ThisWorkbook.Path & "\output.csv"

End Sub

例如，如果要避免output.csv每10分钟被覆盖一次，则可以将当前日期时间添加到文件名中，例如:

If you want to avoid the output.csv to be overwritten every 10 minutes, you could, for example, add current datetime to the filename like this:

'Save csv file
newBook.SaveAs Filename:=ThisWorkbook.Path & "\output_" & Replace(Replace(Replace(Now, "/", ""), ":", ""), " ", "") & ".csv"

2)将此代码添加到auto.xlsb中的Workbook_Open Sub(在VBA IDE中单击ThisWorkbook工作表，然后从下拉列表中选择Workbook and Open)，然后保存:

2) Add this code to Workbook_Open Sub (click ThisWorkbook sheet in VBA IDE, and select Workbook and Open from the dropdown) in auto.xlsb, and Save:

Private Sub Workbook_Open()

    Call Module1.SaveRangeToCSV

End Sub

每次单击以打开文件时，都会触发SaveRangeToCSV，因此将创建csv.

Every time you doble-click to open the file, SaveRangeToCSV will be triggered and, hence, the csv created.

3)自动执行此文件实际上取决于您的首选项和所使用的操作系统.我假设您使用的是Windows，因此最简单的方法是在Windows的任务计划程序"中创建一个任务，该任务每10分钟运行一次"auto.xlsb".

3) Automating the execution of this file really depends on your preferences and the Operating System you are working on. I'm assuming your are on Windows, so the easiest way to do it would be creating a task in Windows' Task Scheduler which runs "auto.xlsb" every 10 minutes.

我希望这会有所帮助.

这篇关于自动将数据从Excel工作表保存到CSV文件的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！