Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Switching Between Open Workbooks (windows) using Filenames as Variables

0

I have a Macro that opens (2) Workbooks.

The 1st is an Input data Workbook from a .tsv file.

The Macro then Copys a Series of Cells and Colums into the 2nd.

This process is repeated switching between workbooks until completed.

I need the 1st Workbook to be User Selected,(I am currently using the Application.GetOpenFilename) to do this. 

When the Macro has completed I need the Second Workbookds to be saved to a User Defined Filename and Folder. The Application.GetSaveAsFilename should work fine fo this part. I just haven't gotten things to work that far yet to confirm it.

I am having difficulities assiging the 1st File to a Variable that can be referenced throughout the Macro.

Browsing to and Opening the File is easy it is the referencing it in the statement that switches between the Open workbooks:

 Windows("FileToOpen").activate

That is giving me troubles. How do I set the file I opened to a variable to be referenced in the statement above?

Here is a Sample of the Code that switched back and forth:
   

    Windows("FileToOpen").Activate
    Range("D7:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("MT_PresetTemplate_880.xlsx").Activate
    Range("U65").Select
    ActiveSheet.Paste
    
    Windows("FileToOpen").Activate
    Range("F7:F41").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("MT_PresetTemplate_880.xlsx").Activate
    Range("W65").Select
    ActiveSheet.Paste
    
    Windows("FileToOpen").Activate
    Range("C42").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("MT_PresetTemplate_880.xlsx").Activate
    Range("W103").Select
    ActiveSheet.Paste

Any help with this would be great   

excelmacros-vba
MS Office Version: 14071665000
posted by: Rod on Mon Jul 11, 2016 at 11:30 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

If an Answer worked for you! Click the Select Answer button at the bottom of it and then click Yes to verify it.
don (rep: 1989) Jul 12, '16 at 10:13 am
Add to Discussion

Answers

0

You just need to setup the basic syntax for referencing workbooks.

Here is a template for that:

Sub reference_workbooks()

Dim Wb1 As Workbook
Dim Wb2 As Workbook

'Makes it run faster.
Application.ScreenUpdating = False

'Use the full path of the file to open it, including the files extension.
Set Wb1 = Workbooks.Open("C:\sample_1.xlsx")
Set Wb2 = Workbooks.Open("C:\sample_2.xlsx")

'Do stuff with the files
'Wb1 and Wb2 references the workbook and the rest is regular syntax for doing stuff in the workbook
'Reference them like this:
wb1_value = Wb1.Sheets("Sheet1").Range("A1").Value
wb2_value = Wb2.Sheets("Sheet2").Range("A1").Value

'When you are done, close the files.
'False means don't save changes. Change to true if you want.
'Do this for each workbook you want to close.
Wb1.Close SaveChanges:=False
Wb2.Close SaveChanges:=False

'Makes Excel work like normal again.
Application.ScreenUpdating = True

End Sub

I commented it pretty heavily so most of it should be pretty easy to understand but ask if anything isn't clear.

You should try to get this working with a simple set of data on your end and then work it into the larger macro; that will make it easier to troubleshoot any issues that might occur.

posted by: don (rep: 1989) on Mon Jul 11, 2016 at 11:55 pm
Discuss

Discussion

I get a RunTime 91 Error after the Set Wb1 = Workbooks.Open statement
Rod Jul 12, '16 at 9:33 pm
Did you change the file path to point to the correct file on your computer?
don (rep: 1989) Jul 12, '16 at 10:02 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login