Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Activate a worksheet or workbook with a variable name

  Asked By: Darla    Date: Aug 19    Category: MS Office    Views: 6886
  

Can anyone help me? I want to input filenames using input boxes
then activate these workbooks and then join them together. My
(newbie) code is

file1 = InputBox("enter 1st file:")
file2 = InputBox("enter 2nd file:")
file3 = InputBox("enter 3rd file:")

Workbooks.OpenText Filename:=file1 & ".xls"
Workbooks.OpenText Filename:=file1 & ".xls"
Workbooks.OpenText Filename:=file1 & ".xls"

Workbooks(file1).Activate
Sheets(file2).Select
Worksheets(file2).Move Before:=Workbooks(file1).Sheets(1)

but VBA wouldn't let me. How can I have variable worksheet or
workbook names in VBA?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Evelyn Hughes     Answered On: Aug 19

You are on the right "path". You just need to include the full path
of the file  like "C:\dir1\file.xls" when you open it. Something like
this is useful if the files are in the same directory as the macro's
workbook:

p = Application.ThisWorkbook.Path
Workbooks.OpenText Filename:=p & "\" & file1 & ".xls"

You may want to use the FileDialog Object. Search excel help  or
yahoo for more info.

After that, change the following:
Workbooks(file1).Activate
Workbooks(file1 & ".xls").Activate

 
Didn't find what you were looking for? Find more on Activate a worksheet or workbook with a variable name Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post