0

I have 3 workbooks

source workbook

target workbook

reference workbook - (Containing the macro which visible across all workbooks)

Is it possible to change switch between Active workbook ( target workbook) and ( source workbook which was active workbook).

  • Activate doesn't seem to help me, I do not if this is a bug or what it is. I have stopped in this step for quite sometime now.

  • This workbook function takes me back to reference workbook.

Hope my question is clear. Appreciate your help.

' My code is in a test macroworkbook
' I am having a workbook opened 1.xlsx
' Opening a workbook countrypricelist.xls
'running the code from 

Dim sourcewb As Workbook
Dim targetWorkbook As Workbook
Dim filter As String
Dim filter2 As String
Dim rw As Long
Dim x As Range
Dim y As Range


Set sourcewb = ActiveWorkbook
Set x = sourcewb.Worksheets(1).Range("A:F")
Dim sourceSheet As Worksheet
Set sourceSheet = sourcewb.Worksheets(1)
MsgBox sourceSheet.Name
x.Select

MsgBox sourceSheet.Name

x.Select


MsgBox sourcewb.Name ' This gives me sourceworkbook name.


filter = "(*.xls),*.xls"


Caption = "Please Select an input file "


Application.ScreenUpdating = False


Filename = Application.GetOpenFilename(filter, , Caption)

Set targetWorkbook = Application.Workbooks.Open(Filename)

Set y = targetWorkbook.Worksheets(1).Range("A:F")

y.Select

Dim targetSheet As Worksheet

Set targetSheet = targetWorkbook.Worksheets(1)

MsgBox targetSheet.Name


Set targetWorkbook = ActiveWorkbook  


 MsgBox targetWorkbook.Name 'This gives me target workbook name


y.Select
sourcewb.Activate

MsgBox sourcewb.Name ' Source workbook becomes same as targeworkbook.
x.Select 

MsgBox sourcewb.Name & " This is the source workbook "
MsgBox targetWorkbook.Name & " This is the target workbook "


With sourcewb.Worksheets(1)
For rw = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Cells(rw, 3) = Application.VLookup(Cells(rw, 2).Value2, x, 3, False)
    Cells(rw, 4) = Application.VLookup(Cells(rw, 2).Value2, x, 4, False)
    Cells(rw, 5) = Application.VLookup(Cells(rw, 2).Value2, x, 5, False)
Next rw
End With


MsgBox "All required columns from source mapped to target file "

MsgBox "Trying to map from target to source "

Set sourcewb = ActiveWorkbook
MsgBox ActiveWorkbook.Name

Application.ScreenUpdating = False

So If I change the line sourcewb = Thisworkbook my reference is changed to source code to workbook which is not my desired workbook as it contains many other macros for other activities. Hope this is code is fine.

Sai Nagarajan M M
  • 45
  • 2
  • 7
  • 1
    Dont know maybe because the "." before cells is missing. Please Format your question i dont want to read through this the way its looking right now. Even finding your question was difficult... – Max Jun 16 '17 at 08:31
  • Please edit your question and use markdown for code and for quoting text blocks. If you can't be bothered to learn how to use the site, then we can't be bothered to spend our time on solving your problem. – teylyn Jun 16 '17 at 08:44
  • I have edited the question. Hope it is understandable. Apologise my bad articulation ability. @Max – Sai Nagarajan M M Jun 16 '17 at 09:33
  • 1
    I think you're confusing yourself with worrying which is the Activeworkbook - in pretty much all cases there's no need to select the workbook that you want the code to run against. Microsoft says the Activeworkbook _returns a Workbook object that represents the workbook in the active window (the window on top)._ `ThisWorkbook` is always the workbook containing the code. Have a look at this link to explain referencing without selecting: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Darren Bartrup-Cook Jun 19 '17 at 09:25
  • Thanks this was helpful. I manged with this :) – Sai Nagarajan M M Jun 19 '17 at 10:38

1 Answers1

1

The Excel Workbook Object allows you to programatically open, edit and close any workbook, not just the currently 'Activated' one.

Example:

Dim wb as Excel.Workbook, otherwb as Excel.Workbook
Dim ws as Excel.Worksheet, otherws as Excel.Worksheet
Set wb = Workbooks.Open "somefile.xlsx"
Set otherwb = Workbooks.Open "otherfile.xlsx"
Set ws = wb.Sheets(1)
Set otherws = otherwb.Sheets(1)

' do stuff
ws.Cells(1,1) = otherws.Cells(1,1)

'save changes
wb.Save
serakfalcon
  • 3,501
  • 1
  • 22
  • 33
  • Yes it works with "Open" command as it makes the opened workbook as the Active workbook. I do not want "Workbook.Open" because my files are quite big close to 20-30 MB approx with more than 2 Million lines and 160 rows. By the time both the files open system goes to Sleep and Macro fails. So looking for other options. – Sai Nagarajan M M Jun 19 '17 at 07:09
  • @serafalkan if you can help on this – Sai Nagarajan M M Jun 19 '17 at 07:43
  • Yes added @serafalkan. – Sai Nagarajan M M Jun 19 '17 at 08:11
  • I don't think you can solve this in Excel; The only alternative within Excel would be to open a new instance of Excel in the background however that doesn't solve your problem since the scale issue is still there. With your scale I would instead import the data into Access and perform the manipulations there, and export back to Excel when done. Note importing 2million rows is going to be slow. At that size you should be asking yourself whether you should migrate to another platform e.g. MySQL, SASS, or at least Access... – serakfalcon Jun 19 '17 at 08:43
  • Thanks for your thoughts and cannot have other tools in my system installed as this is a not a personal system and I am not a Techie. I am trying to reduce the manual work the team is doing in excel. Will try to convince with multiple Macros. Ok last query I read in lots of blogs that *.xlsx .activate is not working in the latest excel version. Is it true ? – Sai Nagarajan M M Jun 19 '17 at 08:50
  • I try to avoid relying on Activate as in my experience it has unreliable results when being run from a macro. (for example, if the user changes focus themselves while the macro is running it may activate a different sheet; the sheet being Activated may not actually be able to activate until the macro is complete etc.). Instead referring to the workbook and worksheet as objects will let you manipulate them without worrying about what is activated. – serakfalcon Jun 19 '17 at 08:57