Help with workbook source and textbox value

[Seve]

Hi there,

First and foremost - I'm rather new to VBA, and have only scratched the surface of the wonderful potential that VBA offers. Therefore, pardon me if my description might lack a bit :)

Currently (in MS Excel 2010), I am trying to create 3 x userforms where I have a GUI to select a specific excel file from a path directory, and then the whole path is pasted and shown in a textbox in each of the three userforms.

All of this is working flawlessly.

Now when I want to click my command button to open up all three different workbooks to copy a specific data range and paste it into my current sheet, it merely just freezes Excel (I get no error message).

Below is shown a code example of my VBA module to go through the operation - Here utilizing the path that I pasted in a textbox from one of the userforms (as a note here - I named each textbox from each userform as textbox1, textbox2 and textbox3 to avoid confusion).



Sub Update_CM_Eng()
Dim wbSource As Workbook
Dim wbDestination As Workbook


'open the source workbook from textbox value from userform and select the source sheet
Set wbSource = Workbooks.Open(TextBox1.Value)


'Set the destination workbook variable
Set wbDestination = Workbooks("Recruitment performance.xlsm")


'copy the source range
wbSource.Sheets("recruit_data").Range("$A$2:$N$150").Copy


'paste the value at range
wbDestination.Sheets("Applicants_CM_Eng").Range("A2:N150").PasteSpecial (xlPasteValues)


Application.CutCopyMode = False


ActiveWorkbook.Save


Workbooks("recruit_data.xls").Close SaveChanges:=False


Sheets("Total Overview").Select


End Sub

Essentially when I click my command button, I call upon the three modules that I've created (code example as shown above).

My question is therefore: How do I set my source workbook (wbSource) as the workbook that I specified in the textbox value from a userform in the above code? :)

Thank you very much in advance!

[mdmackillop]

Dim wbSource As Workbook
Dim Destination As Range
Set Destination = Sheets("Applicants_CM_Eng").Range("A2:N150")
Set wbSource = Workbooks.Open(TextBox1.Value)
Destination.Value = wbSource.Sheets("recruit_data").Range("$A$2:$N$150").Value
wbSource.Close SaveChanges:=False
ActiveWorkbook.Save
Sheets("Total Overview").Activate

[Seve]

Hi,

Thanks for the answer. However it unfortunately doesn't work. It seems that it cannot read and pass the 'TextBox1.Value'. However, I tried to output the textbox1 value to a specific cell into the spreadsheet (hereby pasting the directory path into the cell), and changed the 'textbox1.value' to that cell reference, and wierdly the code now works.

Can you explain why it won't read the textbox1.value when I use that command?

Thanks in advance!

[mdmackillop]

Can you post your workbook. Use Go Advanced, Manage Attachments

[Seve]

Here you go :)

Sorry if it is messy ^^

Again - First timer VBA :P

[mdmackillop]

Not much time just now.
The macro will not access the closed form. Pass the form data to the macro as follows or add the sub to the Userform where you can reference the textbox directly.


Private Sub TextBox1_Change()
Call Update_CA_Eng(TextBox1.value)
End Sub

Sub Update_CA_Eng(Data)
Dim wbSource As Workbook
Dim wbDestination As Workbook


'open the source workbook and select the source sheet
'Set wbSource = Workbooks.Open( _
Filename:="J:\Global Engineering Sourcing\1 Administration\Employees\Recruitment\Screening\Data\Applications\CA_Enginee ring\recruit_data.xls")


Set wbSource = Workbooks.Open(Data)


'Set the destition workbook variable
Set wbDestination = Workbooks("Recruitment performance.xlsm")


'copy the source range
wbSource.Sheets("recruit_data").Range("$A$2:$N$150").Copy


'paste the value at E9
wbDestination.Sheets("Applicants_CA_Engineering").Range("A2:N150").PasteSpecial (xlPasteValues)


Application.CutCopyMode = False


ActiveWorkbook.Save


Workbooks("recruit_data.xls").Close SaveChanges:=False


Sheets("Total Overview").Select


End Sub