What I can't do is get the name of the damn file they select so I can pull data from it.

The following is pretty simple it gets the open dialog to asks them what to open. If its open it asks them if they want to re-open it. If you select no at this point it skips to the next part. That part works great.

All I want to do now is get the name of the file that was selected so that I can copy paste the data out of it but I can't seem to get thet working. If you select "don't reopen" the file is not active so I can't use that little trick.

Nope still doesn't work, Here's a better explained code, its the last three lines I need help with. I just need to extract that damn file name! :)

Dim OpenFileName As Variant Dim wBook As Workbook Dim SampleReg As Workbook On Error Resume Next 'If it stuffs out go to the next step ActiveWorkbook = SampleReg 'Sets the current workbook as the sampleregister OpenFileName = Application.GetOpenFilename() 'Opens the File Dialog for the person to select the file to open If OpenFileName = False Then Exit Sub 'If the person clicks cancel it stops Set wBook = Workbooks.Open(OpenFileName) 'The workbook has been selected and this get the name of that workbook wBook.Activate 'This should activate the workbook that was just opened (or was already open) SampleReg.ActiveSheet.Range("P6") = OpenFileName 'The file name should be put in this cell to show that its working

Seems to work except when you don't open the workbook (i.e. its already open and you click don't reopen) but thats OK I'm getting really frustrated with this so I'll just tell people to make sure the file is shut or it won't work. It should be so simple!

I still can't seem to get the bloody thing to work though.

It seems to be getting to the last bit and thats as far as it gets. I need to copy data from the new workbooks (wBook) worksheet "UllB4" (is the sheets Codename - Sheets names get renamed so need to use code name.)

Dim OpenFileName As Variant Dim wBook As Workbook Dim SampleReg As Workbook' On Error Resume Next 'If it stuffs out go to the next step Set SampleReg = ActiveWorkbook 'Sets the current workbook as the sampleregister OpenFileName = Application.GetOpenFilename() 'Opens the File Dialog for the person to select the file to open If OpenFileName = False Then Exit Sub 'If the person clicks cancel it stops Set wBook = Workbooks.Open(OpenFileName) 'Set the opened workbooks name Set SampleRegData = SampleReg.Worksheets("Data") 'Set the data pagewBook.UllB4.Range("X10:AH145").Copy 'Copy the data SampleRegData.Range("A2:AH145").PasteSpecial xlPasteValues

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.