Open a Workbook and Transfer Data to a New Sheet in Excel VBA - CODE INCLUDED скачать в хорошем качестве

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

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