Pull Data from a closed/open excel workbook

[Masterimp]

Hello Everyone,

I am trying to build a Macro that will allow a user to select an excel file and then select a sheet within the selected workbook. The macro will then pull data from the worksheet selected and paste it into a user form, so the user can check that the data that was pulled is correct. The user form will then populate this data into a row within the open workbook. I already have the latter part of the code in place and it is working. For the front half of this project I have build the following code


Private Sub Okay_Click()
Dim TgtName As String
Dim WB As Workbook
Application.ScreenUpdating = False
TgtName = Trim(Storyboard.Value) 'Storyboard.value is a userform textbox value'
If Len(TgtName) = 0 Then Exit Sub
Dim DirFile As String
DirFile = Storyboard.Value
If Len(Dir(DirFile)) = 0 Then
MsgBox "File does not exist"
Else
Set WB = Workbooks.Open(DirFile, True, True)
With ThisWorkbook.Worksheets("Definitions")
.Range("H1").Value = WB.Worksheets(Sheet.Value).Range("J3") 'Sheet.value is a userform textbox value'
.Range("H2").Value = WB.Worksheets(Sheet.Value).Range("D4")
.Range("H3").Value = WB.Worksheets(Sheet.Value).Range("D6")
.Range("H4").Value = WB.Worksheets(Sheet.Value).Range("D1")
.Range("H6").Value = WB.Worksheets(Sheet.Value).Range("D7")
.Range("H7").Value = WB.Worksheets(Sheet.Value).Range("J4")
.Range("H8").Value = WB.Worksheets(Sheet.Value).Range("I6")
.Range("H9").Value = WB.Worksheets(Sheet.Value).Range("O22")
.Range("H10").Value = WB.Worksheets(Sheet.Value).Range("S21")
End With
WB.Close False
Application.ScreenUpdating = True

End If


Unload Me
Reference.Hide
MsgBox "Please check the data that was pulled from the storyboard worksheet", vbInformation, "Data Check"
NewProject.Show

I have build a textbox with opens up a dialog box to select the workbook path for this code. This half of the code is working and as it currently sits paste's the pulled data into a hidden worksheet, so the userform will initialize with the data that was pulled. The problem is that I am making the user enter the sheet name on a user form before opening the workbook to pull the data. The code uses the sheet name within the text box to pull the data from the appropriate sheet. I would like to prompt the user with all the sheet names from the selected workbook and let them select the one to use, as all the sheets from the specified workbook will contain similar data and a certain one will have the most up to date data. This will make the code more user-friendly which is one of the objectives I am seeking with this project.

I understand that this is a mouthful, but if there is anyhelp any of you could provide, that would be greatly appreciated :)

[GTO]

...I would like to prompt the user with all the sheet names from the selected workbook and let them select the one to use, as all the sheets from the specified workbook will contain similar data and a certain one will have...

Hi there,

I am not exactly following all of it, but for the current challenge, I would try:


Use a listbox for the sheet names rather than the textbox.
Populate this with all the sheet names after opening the workbook.


Hope that helps,

Mark

[Masterimp]

That is what I thought I could do, but the problem is that the macro only runs after the Okay button is pushed on the user form. So the data has to already be inputed before it is ran. Is there a way to get the sheet name before I run the macro?

[GTO]

Off to bed, but...

Presumably Storyboard.Value has either the FullName, or, Name of a workbook in the current directory. Could you just add another command button to do the first bit (open the file, get the sheet names), then enable (on success) the current command button?

You may wish to post the workbook, as it may well be easier for any 'answerer' to give better suggestions that way.

Mark