-1

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

BertG3
  • 19
  • 1
  • 3
  • I tried a lot of codes that I found on the internet but I don't succeed. I know that you expect more from me but I am not so comfortable with VBA. I saw this but this isn't time-based http://stackoverflow.com/questions/37037934/excel-macro-to-export-worksheet-as-csv-file-without-leaving-my-current-excel-sh – BertG3 Jan 18 '17 at 22:19
  • Please read the help center article on [how to ask a question](http://stackoverflow.com/help/how-to-ask) on SO. SO is not here to do your work for you, only to help you with specific programming errors. A single line is not an appropriate question for SO. – Taelsin Jan 18 '17 at 22:21
  • Okay I am very sorry. Thanks for your answer. I can program but not in VBA. I'll need fp hire someone to help me – BertG3 Jan 18 '17 at 22:24

1 Answers1

3

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

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

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) 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

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

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.

I hope this helps.

lolog
  • 180
  • 2
  • 9