Open A Single File Export the Data with VBA

Opening a file with Excel VBA and putting the data from your current file in the newly opened file is known as exporting data. This is done in much the same way as the prior example Import Data with VBA and is achieved by changing some of the variables. The newly exported data is saved once the VBA process is complete. The following is an example;


Option Explicit
Sub OpenExp() 'Excel VBA to open a specific workbook and export information.
Dim owb As Workbook
Dim sh As Worksheet

Set sh=Sheet1
sh.Range("A1:F100").Copy
'Open a file called England (Change to suit).
Set owb=Workbooks.Open("C:\Test\England.xlsm")

owb.Sheets("Data").Range("A1").PasteSpecial xlPasteValues
owb.Close True 'Close opened workbook save (very Important to Save)
End Sub


The above Excel VBA procedure takes the data in the workbook your starting workbook, copies it, it then opens a workbook called England and pastes the values in cell A1. The workbook is then saved and the workbook is closed (Close True - is for saving with close). The next time the England.xls workbook is opened the new data will be contained within it. Ensure the data is correct in relation to the file path.