Clicky

Hi Experts,

I need Experts help create a macro which is able to export only “Data” sheet from this workbook (attached) to “D:\File Recovery”.  The exported sheet only copied data from Column A: D. The copied file need to be named as “Record” + data from cell A3 (e.g. Record Ver-1).  Hope Experts could help.


asked 12/14/2011 04:33

Billa7's gravatar image

Billa7 ♦♦


11 Answers:
What is the name of the workbook you want to export to?
In the code below I named the workbook in D:\File Recovery as 'File Recovery.xlsx'.
Change the code to the appropriate name.

Sub Export()
    Dim V_PartOfSheetName As String
   
    Application.ScreenUpdating = False
   
    V_PartOfSheetName = Range("A3").Value
   
    ChDir "D:\File Recovery"
    Workbooks.Open Filename:="D:\File Recovery\File Recovery.xlsx"
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Data " & V_PartOfSheetName & ""
    Windows("Export.xls").Activate
    Columns("A:D").Select
    Selection.Copy
    Windows("File Recovery.xlsx").Activate
    ActiveSheet.Paste
    ActiveWindow.DisplayGridlines = False
    Range("A3:C3").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Range("A3:C3").Select
    MsgBox "Data is exported."
    Application.ScreenUpdating = True
End Sub
link
's gravatar image


Hi,

Shows run time error at this line "Workbooks.Open Filename:="D:\File Recovery\File Recovery.xlsx"
Please assist.
link
's gravatar image


This line of code will open the workbook you want to export to.
So you should have a workbook named 'File recovery.xlsx' in folder 'D:\File Recovery'.
Check if the workbook is there. If it's not there create a blank workbook with the name 'File recovery.xlsx'

If you already have a workbook there for exporting, change the line of code
Workbooks.Open Filename:="D:\File Recovery\YourWorkbookName.xlsx
link
's gravatar image


Hi Davy2270,

Is that a way for us export the same workbook but removing all other worksheets but the same time retaining only Column A:D data (Data sheet)?
link
's gravatar image


I'm sorry Billa, I do not understand your question fully?

Do you want to make another export besides the first one?
In which file you want to remove all other worksheets? The file named Export? Or the file you want to export to?

I also noticed you have Excel version 2003, so the code needs to be adjusted a bit:

Sub Export()
    Dim V_PartOfSheetName As String
   
    Application.ScreenUpdating = False
   
    V_PartOfSheetName = Range("A3").Value
   
    ChDir "D:\File Recovery"
    Workbooks.Open Filename:="D:\File Recovery\File Recovery.xls"
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Record " & V_PartOfSheetName & ""
    Windows("Export.xls").Activate
    Columns("A:D").Select
    Selection.Copy
    Windows("File Recovery.xlsx").Activate
    ActiveSheet.Paste
    ActiveWindow.DisplayGridlines = False
    Range("A3:C3").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Range("A3:C3").Select
    MsgBox "Data is exported."
    Application.ScreenUpdating = True
End Sub
link
's gravatar image


Just one extra minor change:
Sub Export()
    Dim V_PartOfSheetName As String
   
    Application.ScreenUpdating = False
   
    V_PartOfSheetName = Range("A3").Value
   
    ChDir "D:\File Recovery"
    Workbooks.Open Filename:="D:\File Recovery\File Recovery.xls"
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Record " & V_PartOfSheetName & ""
    Windows("Export.xls").Activate
    Columns("A:D").Select
    Selection.Copy
    Windows("File Recovery.xls").Activate
    ActiveSheet.Paste
    ActiveWindow.DisplayGridlines = False
    Range("A3:C3").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Range("A3:C3").Select
    MsgBox "Data is exported."
    Application.ScreenUpdating = True
End Sub
link
's gravatar image


Billa, I think I understand now what you want to do. You want to save the Export.xls file to D:\File Recovery
That file should have only 1 sheet named 'Record + Value from cell A3.
Only columns A to D.
You can do this but the saved file must have another name, in the code below I used the name 'Exported.xls'

It's also very important that the length of the value in cell A3 does not exceed 25 characters. The maximum length of a sheetname in excel is 31 characters. "Record " + 25 extra characters = 31 characters in total.

Try it:

Sub Export()
    Dim V_PartOfSheetName As String, V_WorkbookFullName
   
    Application.ScreenUpdating = False
   
    V_PartOfSheetName = Range("A3").Value
    V_WorkbookFullName = ActiveWorkbook.FullName
   
    ActiveWorkbook.Save
    ChDir "D:\File Recovery"
    Application.DisplayAlerts = False
    ' Save the file to D:\File Recovery
    ActiveWorkbook.SaveAs Filename:="D:\File Recovery\Exported.xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    Application.DisplayAlerts = True
    'rename sheet
    ActiveSheet.Name = "Record " & V_PartOfSheetName & ""
    'only columns A to D have data
    Columns("E:H").Clear
    'Delete all sheets except the first
    Application.DisplayAlerts = False
      For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
         If Worksheets(I).Name <> "Record " & V_PartOfSheetName & "" Then _
            Worksheets(I).Delete
      Next I
    Application.ScreenUpdating = True
    Range("A3:C3").Select
    're-open the original file
    Workbooks.Open Filename:="" & V_WorkbookFullName & ""
    MsgBox "Data is exported."
   
    'close the saved file D:\File Recovery\reported.xls
    Workbooks("Exported.xls").Activate
    Application.ScreenUpdating = True
    ActiveWindow.Close savechanges:=True

End Sub
link
's gravatar image


Hi Davy,

Thanks a lot for the revised code. it works now, but I noticed its copy together with the "button" object. Is that a way for us omit button or picture when executing this macro?
link
's gravatar image


I'm not sure which 'button' you are referring to. Do you mean the 'run sub' play button?
If you want a button on your sheet, which activates the macro when clicked you need to do this.

Draw a rectangle shape on the sheet. Enter some text like 'export' in the rectangle.
Then right click the rectangle border and choose 'Assign macro...'
In the dialogbox choose the macroname 'Export'.
Click Ok.

If you then click the rectangle, the macro will execute.
link
's gravatar image


Hi Davy,

Sorry for the confusion, I'm referring to the run sub play button. The deletion of this button should execute when the "Sub Export" takes place.  
link
's gravatar image


Hi Davy,

Managed to fix the object issue. Thanks a lot for the help.
link
's gravatar image


Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×89

Asked: 12/14/2011 04:33

Seen: 317 times

Last updated: 12/15/2011 09:40