none
VBA to export data to new workbook with current date as workbook name RRS feed

  • Question

  • Dear All

    I have a code as below which I would like to modify a little bit.

    This code works and allows to export data from the excel tool I called it "CENEO quickview" to new workbook without any formulas. But this code is imperfect. I would like to modify parts to:

    - get the exported workbook name as "current date & CENEO quickview" so for exaple:

    "2014_09_08_CENEO quickview"

    "2014_09_09_CENEO quickview" etc

    The main workbook should stay unchange - "CENEO quickview"

    As you can see reading this code I am basic in VBA. Some parts were recorded some pasted from websites.

    The problem is there are to many references to Active workbooks I can handle this alone.

    Thaks for your suggestions

    Sub Copy_sheets()

        ActiveWorkbook.Sheets.Select
        ActiveWorkbook.Sheets.Copy
      Cells.Select
      ActiveWorkbook.Sheets.Select

        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.DisplayAlerts = False
    Sheets("GET DATA").Delete
    Application.DisplayAlerts = True

    Sheets("Database").Select
    'clears database from blanks cells
        ActiveSheet.Range("$A$1:$G$15000").AutoFilter Field:=3, Criteria1:="="
        Rows("2:2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete Shift:=xlUp
        ActiveSheet.Range("$A$1:$G$15000").AutoFilter Field:=3
        Sheets("REPORT").Select
        Range("A1").Select

    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name <> "REPORT" Then WS.Visible = xlSheetHidden
    Next WS


    For i = 1 To ActiveWorkbook.Connections.Count
    If ActiveWorkbook.Connections.Count = 0 Then Exit Sub
    ActiveWorkbook.Connections.Item(i).Delete
    i = i - 1
    Next i


    End Sub




    Monday, September 8, 2014 10:53 AM

Answers

  • Just before the End Sub, use this line

    ActiveWorkbook.SaveAs Format(Date,"yyyy_mm_dd") & "_CENEO quickview.xlsx"

    Your code could certainly be shortened and simplified, but I find that the best way to do that is to not start with your code, but with a description of the task, like:

    "Copy REPORT to a new workbook keeping the formatting but converting to values, then save that workbook with a dated name like "2014_09_08_CENEO quickview" "


    Monday, September 8, 2014 4:58 PM

All replies

  • Just before the End Sub, use this line

    ActiveWorkbook.SaveAs Format(Date,"yyyy_mm_dd") & "_CENEO quickview.xlsx"

    Your code could certainly be shortened and simplified, but I find that the best way to do that is to not start with your code, but with a description of the task, like:

    "Copy REPORT to a new workbook keeping the formatting but converting to values, then save that workbook with a dated name like "2014_09_08_CENEO quickview" "


    Monday, September 8, 2014 4:58 PM
  • It's just like Bernie said.  Also, consider this as a way to create a new folder and save to that.

    'Create folder for the new files
    foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
    MkDir foldername

    This is a great resource for you read, when you have time.

    http://www.rondebruin.nl/win/s3/win006_3.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, September 11, 2014 1:08 PM
  • Gents

    Sorry for late answer. I followed the instructions and report did not open with the expected name.

    I had placed new code line at the end:

    ... (first part of code - no change)

    ActiveWorkbook.SaveAs Format(Date, "yyyy_mm_dd") & "_CENEO quickview.xlsx"

    End Sub

    The output report had a name "book 1" Thats not I expect

    I think problem is in the first part of the code:

    Sub Copy_sheets()

        ActiveWorkbook.Sheets.Select
        ActiveWorkbook.Sheets.Copy

    The code starts from copy active workbook and open its as a new one. Then all modifications take place on a new workbook. What I expect is to the copied active workbook has a name as

    "2014_09_08_CENEO quickview"

    "2014_09_09_CENEO quickview" etc

    thanks for suggestions


    Thursday, September 18, 2014 10:16 AM
  • What version of Excel are you using?
    Thursday, September 18, 2014 12:33 PM
  • Bernie

    Excel 2010

    thanks for help


    Thursday, September 18, 2014 12:59 PM
  • The issue may be this - the "Exit Sub" part specifically, which may cause you to not get to the file save as line:

    For i = 1 To ActiveWorkbook.Connections.Count
    If ActiveWorkbook.Connections.Count = 0 Then Exit Sub
    ActiveWorkbook.Connections.Item(i).Delete
    i = i - 1
    Next i

    Try it this way instead

    If ActiveWorkbook.Connections.Count <> 0 Then
        For i = ActiveWorkbook.Connections.Count To 1 Step -1
            ActiveWorkbook.Connections.Item(i).Delete
        Next i
    End If


    Thursday, September 18, 2014 1:35 PM