PDA

View Full Version : Solved: Activate open workbook



Rejje
11-25-2010, 05:44 PM
Hi and now to the basics! What must i write in order to activate the open workbook called Range("V_60100").Value?

Range("V_60100").Value = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\HKM\HKM.xlsm


Sub ImporteraHKM()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Workbooks.Open Range("V_60100").Value
ThisWorkbook.Activate

For Each cell In Range("IMPORTLISTA")
Range(cell.Value).Copy
' ACTIVATE OPEN WORKBOOK Range("V_60100").Value
' DOESN'T WORK: Workbooks(Range("V_60100").Value).Activate

' do some more stuff later
Next cell

Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("HKM är uppdaterad!")

End Sub

Blade Hunter
11-25-2010, 07:26 PM
Hi and now to the basics! What must i write in order to activate the open workbook called Range("V_60100").Value?

Range("V_60100").Value = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\HKM\HKM.xlsm


Sub ImporteraHKM()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Workbooks.Open Range("V_60100").Value
ThisWorkbook.Activate

For Each cell In Range("IMPORTLISTA")
Range(cell.Value).Copy
' ACTIVATE OPEN WORKBOOK Range("V_60100").Value
' DOESN'T WORK: Workbooks(Range("V_60100").Value).Activate

' do some more stuff later
Next cell

Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("HKM är uppdaterad!")

End Sub

The path is in there which you don't use when activating, try this instead:

Workbooks(Trim(Right(Replace(Range("V_60100").Value, "\", Application.WorksheetFunction.Rept(" ", 256)), 256))).Activate

Replace all "\" with 256 spaces (256 is max length of a file or folder name) then take the right 256 chars which we are guaranteed holds the filename and a bunch of spaces then trim the spaces

mohanvijay
11-25-2010, 11:00 PM
try this



Dim file_piri As Variant
Dim file_posi As Integer
Dim file_name As String

file_piri = Split(Range("V_60100").Value, "\")
file_posi = UBound(file_piri)
file_name = file_piri(file_posi)
Workbooks(file_name).Activate

Bob Phillips
11-26-2010, 03:26 AM
Replace all "\" with 256 spaces (256 is max length of a file or folder name) then take the right 256 chars which we are guaranteed holds the filename and a bunch of spaces then trim the spaces

Better to use InstrRev and take it from the last \ I would have thought.

Rejje
11-26-2010, 05:43 AM
Hmm... All these suggestions are a bit complicated for me since my vba-experience is some 7 days old.

I've tried above suggestions but I don't get it to work. I feel it might be better to explain furhermore what I'm trying to achieve with the code.

1. I'm working from a workbook called either:
A. Range("V_50100").Value = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOST MALL\KUNDPOST MALL.xlsm = "ThisWorkbook"
B. Range("V_50200").Value = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOSTER\KUNDPOST-xxxxxxxxxx.xlsm = "ThisWorkbook"
The reason why it can be infinitely of different names is that A is the template and B is template saved as something else. So I think its best if the macro always refer to A & B as "ThisWorkbook" or something similar.

2. I want to update/import values to a lot of named areas into "this workbook" from another workbook called Range("V_60100").Value = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\HKM\HKM.xlsm = "OtherWorkbook" (I know that's not a valid function!)

3. The list is an area called "IMPORTLISTA" and is located in "ThisWorkbook" and which contains cells with the names of areas to import to in different worksheets in "ThisWorkbook".

4. All names off cells/merged cells/areas in list "IMPORTLISTA" of course has an equivalent in "OtherWorkbook" with exactly the same shape and name as can be found in "ThisWorkbook".

5. Only values should be imported - NOT format etc.

6. So the code should achieve:


don't calculate workbooks or show what's happening
open "OtherWorkbook" in case it's not allready open.

go to "IMPORTLISTA in ThisWorkbook"

read name of "named area 1"/ go to "named area 1 in OtherWorkbook"/ copy values from "named area 1 in OtherWorkbook"/ paste values into "named area 1 in ThisWorkbook"

then go to next cell in "IMPORTLISTA in ThisWorkbook"

read name of "named area 2"/ go to "named area 2 in OtherWorkbook"/ copy values from "named area 2 in OtherWorkbook"/ paste values into "named area 2 in ThisWorkbook"

continue doing this through all cells in "IMPORTLISTA in ThisWorkbook"

close "OtherWorkbook" in case it wasn't open when sub started
calculate workbooks and show what's happening

show MsgBox "HKM has been imported!"


I'm greatful in case you show me how this could be done in as simple and understandable code as possible since I'm really trying to learn vba with this project.

Below is again what I've figured out so far.



Sub ImportHKM()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Workbooks.Open Range("V_60100").Value
ThisWorkbook.Activate

For Each cell In Range("IMPORTLISTA")
Range(cell.Value).Copy
' ACTIVATE OPEN WORKBOOK Range("V_60100").Value
' DOESN'T WORK: Workbooks(Range("V_60100").Value).Activate

' do some more stuff later
Next cell

Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("HKM has been imported")

End Sub


Thank all of you for all support so far!

Bob Phillips
11-26-2010, 06:54 AM
Does this not work?



Sub ImportHKM()
Dim OtherWB As Workbook
Dim cell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set OtherWB = Workbooks.Open(Range("V_60100").Value)

With ThisWorkbook.Worksheets(1)

For Each cell In .Range("IMPORTLISTA")

.Range(cell.Value).Copy
OtherWB.Worksheets(1).Range(cell.Value).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Next cell
End With

Range("A1").Select

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic
MsgBox ("HKM has been imported")

End Sub

My only concern is whether all ranges are on sheet 1?

Rejje
11-26-2010, 07:21 AM
I'm afraid it's not on sheet 1. In fact ranges are on 3 different worksheets.

I just got an idea: When recording a macro one get this code when selecting and copying one of the cells within "IMPORTLISTA" then pasting that value into the little thing that show what cell is selected (I don't know the English term for that) and then hit enter.

Do you think this could be something to work on or am I totally lost??

Application.Goto Reference:="HKM_10100"

maybe something like:

Application.Goto Reference:=Range(cell.Value)

Bob Phillips
11-26-2010, 07:57 AM
No, I think that will drive you round and round in circles.

This should work regardless of the sheets the names apply to



Sub ImportHKM()
Dim OtherWB As Workbook
Dim NamesList As Range
Dim Source As Range
Dim target As Range
Dim cell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With ThisWorkbook

Set NamesList = .Names("IMPORTLISTA").RefersToRange

Set OtherWB = Workbooks.Open(Range("V_60100").Value)

For Each cell In NamesList

Set Source = .Names(cell.Value).RefersToRange
Source.Copy
Set target = OtherWB.Names(cell.Value).RefersToRange
target.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Next cell
End With

Range("A1").Select

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic
MsgBox ("HKM has been imported")

End Sub

Rejje
11-26-2010, 08:31 AM
Code works perfect! But lol - first it imported the wrong way; from ThisWorkbook to OtherWB. I changed as below and then PERFECT!

For Each cell In NamesList

Set Source = OtherWB.Names(cell.Value).RefersToRange
Source.Copy
Set target = .Names(cell.Value).RefersToRange
target.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Next cell

Once again: Many thanks! I also start understanding this a bit more now. I see its really smart to dim first as well.

Bob Phillips
11-26-2010, 08:49 AM
You can get VBA to demand that you Dim everything by adding

Option Explicit

at the start of each module.

Rejje
11-26-2010, 05:57 PM
You can get VBA to demand that you Dim everything by adding

Option Explicit

at the start of each module.

Ok but why would that be useful?

Norie
11-26-2010, 08:09 PM
Because it would force you do declare all your variables - which is a good thing.

One of the major advantages is that it'll pick up any typos, eg misspelling of variable names.

That might sound trivial but believe me it's not - I've seen plenty of code fall down just because something has been misspelled.:eek:

It's an easy mistake to make but not always an easy mistake to find and fix.:)

Bob Phillips
11-27-2010, 03:29 AM
Ok but why would that be useful?

VBA is lax in that it is not a strongly typed language and it does not demand declaring all variables. The former we can't do much about, in fact we sometimes exploit this fact, but the latter is avoidable as I mentioned.

Why do it? Because it traps mistakes, such as called a variable myGadget at one place in the code, and myGagdet in another. Without Option Explicit, VBA will happily work with these as two very different variables, and whatever value the first contains will be irrelevant when you get to the second. In addition, I have often seen code where the variable is declared in one procedure, and then the coder tries to use that variable in another procedure. In that latter procedure it is not within scope, but without Option Explicit VBA will work quite happily with it. SO Option Explicit allows one to be much smarter with variable scope, aiding design as well as running.

You can get VBA to automatically add that to each module, by going to Tools>Options>Editor, and check the box 'Require Variable Declaration'. Whilst you are at it, uncheck the box 'Auto Syntax Check'.

Rejje
11-27-2010, 08:14 AM
Ok - I see this is something I really need to consider. Not being used to script it first dim first got very confusing but no I've learned to look for the dim and the set.

In case I try to think a little further: Wouldn't it the be very useful to declare all variables in one place somewhere and then use them "globally" (I don't know the term but I think you will understand)?

But, maybe one shouldn't set the variables "globally" but leave it for the subs?

Norie
11-27-2010, 09:11 AM
You could set all the variables globally but you would need to be careful how you used them.

Also if you are going to be using the variables in different subs you need to watch out for scope.

I don't know of any hard and fast rules but perhaps just use global variables for values that are going to be set once in the code and not altered by the code later

If the value is never going to be altered by the code you could use constants instead of variables.

Bob Phillips
11-27-2010, 09:54 AM
Ok - I see this is something I really need to consider. Not being used to script it first dim first got very confusing but no I've learned to look for the dim and the set.

In case I try to think a little further: Wouldn't it the be very useful to declare all variables in one place somewhere and then use them "globally" (I don't know the term but I think you will understand)?

But, maybe one shouldn't set the variables "globally" but leave it for the subs?

No it wouldn't, you should aim to restrict your variable scope as much as possible.

You have 3 scopes of variable,

Public - are declared as type Public, or Global, and declared outside of a procedure. These will retain their value as long as Excel is running and you do not have an error in your app. These have the widest scope

Module - are declared as Private and declared outside of a procedure. These will also retain their value as long as Excel is running and you do not have an error in your app, but they cannot be accessed by code in another module.

You have to think of the overall design of the application and decide what variables need to be at what scope.

Procedure - these are simple Dim statements in your procedure and are restricted in scope to that procedure. They cannot be accessed in another procedure, in that or any other module.