Open a Workbook and Transfer Data to a New Sheet in Excel VBA - CODE INCLUDED

Опубликовано: 06 Октябрь 2024
на канале: EverydayVBA

11,880

Code and Downloadable File
https://chrisjterrell.com/blog/226224...
Free Quick Reference Guide
https://www.chrisjterrell.com/excel-v...

This video will be looking at opening a file within Excel VBA using the file selector. Now it's super easy to do this, especially if you have the file path. It's easy to do it if you're using the file dialog. But what fun is that? So I'm going to show you how to open a file, go and grab some data from that file, add a new sheet and then take that data from the file that you just opened and put that in the original file. It is more fun, and you'll learn a little bit more in the process.

So let's dig in and get started. We have two Macros. The first shows you how to use the filedialog. We use Application.getOpenFile to get the string of the file you are looking to open. In this code, we are only showing the xlsx files because of this sting "Excel File Only, *.xlsx" and the title of the window is "Select a File to Open." The first thing we do is show you how "cancel" is handled. Clicking "Cancel returns "False." We use an if statement to exit the sub if the string is equal to "false." Next, we pick a file, open the file and then close it.

Now the magic happens. We open a workbook and get data from the first worksheet. Then we add a worksheet to the original workbook and copy the data. First, we declare objects which are going to be all my base objects. I dim my objects so they can be assigned. To do this, we need three objects that for "Thisworkbook" and we need two for the workbook we open.

To get the dialog of the file picker, we use Application.GetOpenFileName so we can select the file. We need to use an if statement that will exit the macro if the user clicks "Cancel" on the file dialog.
We do two things when we open the file, we open the file and assign it to our workbook object. Once the workbook is open, we set our range equal to the data in the first sheet of the workbook using the "CurrentRegion."

The next step is to add a new sheet for the data and rename it using a timestamp. We make the ranges the same size by using the resize function, and then we set the range on the opened workbook equal to the range on the new sheet we just added.
And we are done!!

'======CODE===========================

Sub OpenFile()

Filename = Application.GetOpenFilename("Excel File Only, *.xlsx", 1, "Select A file to Open")
Debug.Print Filename

If Filename = False Then
'Youtube doesn't like less than or greater than
Exit Sub
Else
'No Parenthesis because we are not assigning this to an obect
Workbooks.Open Filename
End If

MsgBox "This workbook has been open " & ActiveWorkbook.Name

ActiveWorkbook.Close

End Sub

Sub OpenFileMoveData()

Dim wb As Workbook 'For the Original Workbook
Dim ws As Worksheet 'For the newly Added workbook
Dim rng As Range 'For the range on the new workbook

Dim nwb As Workbook
Dim nrng As Range

Set wb = ThisWorkbook

Filename = Application.GetOpenFilename("Excel File Only, *.xlsx", 1, "Select A file to Open")
Debug.Print Filename

If Filename = False Then
Exit Sub
End If

'This uses parethesis because you are assigning the object
Set nwb = Workbooks.Open(Filename)
nrng = nwb.Sheets(1).CurrentRegion

Set ws = wb.Sheets.Add
ws.Name = Format(Date, "MM-DD-YY HH MM SS") 'Rename the Sheet to be a timestamp

Set rng = ws.Cells(1, 1).Resize(nrng.Rows.Count, nrng.Columns.Count) 'Set the range size equal to the size of the openned workbook
'show your work :)
wb.Activate
ws.Activate
rng.Select

rng = nrng 'THE MAGIC - this is equivalent to copying

End Sub