Thread: use text from textbox to indicate folder or worksheet name

All,

I'm struggling with the following: I want to copy data from one workbook to another. Instead of programming the folder and workbook name as public constant in VB, I would like have textboxes in Excel where these folder and workbook names can be given. How do I do this?

This is how I programmed it now in a module, followed by some public subs to open the source workbook, copy the data etc. called by a commandbutton:

Public Const source_dir = "D:\My Documents\03 Klanten\Eurovet\data premix\"
Public Const source_file = "OEEpremixweek 2010.14.xls"
Public Const source_tab1 = "Week"
Public Const target_dir = "D:\My Documents\03 Klanten\Eurovet"
Public Const target_file = "Trends OEE Premix v04.xls"
Public Const target_tab1 = "historie"

In other words: in a sheet in Excel the user should be able to indicate in 4 textboxes what is going to be the source_dir, source_file, target_dir and target_file.

Hi

Do you want the user to have four places to place the information or do you want them to browse and click on the one file they want?

In the former case, I think there are at least two options:

1. Use mulitple input boxes (Application.InputBox) sequentally placed to get all the information you need.
2. Use a userform with four text boxes

For the latter case, I think you can use the Application.GetOpenFilename to get the file name for the original file and destination file.

Having said that, I am sure more experienced users can guide you better than me, but that's the way I would approach this situation.

Good luck,

abousetta

Originally Posted by abousetta

Hi

Do you want the user to have four places to place the information or do you want them to browse and click on the one file they want?

In the former case, I think there are at least two options:

1. Use mulitple input boxes (Application.InputBox) sequentally placed to get all the information you need.
2. Use a userform with four text boxes

For the latter case, I think you can use the Application.GetOpenFilename to get the file name for the original file and destination file.

Having said that, I am sure more experienced users can guide you better than me, but that's the way I would approach this situation.

Good luck,

abousetta

I will use four Textboxes, in which the folder and files names will be entered. The question is how to use this info in VBA;

Public Const source_dir = TextBox1.Text does not work.

What to do?

Try this and see if it does what you need.

Good luck.

abousetta

In textbox1.Text: "D:\My Documents\03 Klanten\Eurovet\data premix\"
In textbox2.Text: "OEEpremixweek 2010.14.xls"
In textbox3.Text: "Week"
In textbox4.Text: "D:\My Documents\03 Klanten\Eurovet"
In textbox5.Text: "Trends OEE Premix v04.xls"
In textbox6.Text: "historie"

Private Sub CommandButton1_Click()
  with workbookds.add(textbox1.Text & Textbox2.Text) 
    sq=.sheets(Textbox3.text).usedrange
    .close False
  end with 
  with workbookds.open(textbox4.Text & Textbox5.Text) 
     .sheets(Textbox6.text).cellse(1,1).resize(ubound(sq),ubound(sq,2))=sq
    .close True
  end with 
End Sub

PS. I would prefer comboboxes. Sukses.