Activate other file

[elyanivson]

Hi,
I have two open excel files.
In my procced I have to navigate between the two.

One of the files that I want to activate is a dynamic name. I'll explain.
The name of the file I want to activate is a string of the current date.('ddmmyyyy').

so, this is what I got so far , and it doesn't work.

Function GetDate() As String
Dim a
a = Date
Dim mystring
mystring = Format(Now(), "ddmmyyyy")
Dim currentdate As String
currentdate = "LPR" + mystring + ".xls"
End Function

and the sub that should activate the file is:

Sub pastedatatoIMPORT()
Dim direction
Dim strSecondFile As String
direction = GetTodayDateLPRFILENAME.GetDate
strSecondFile = "C:\Documents and Settings\yanive\Desktop\" + direction + ""
Windows(strSecondFile).Activate
End sub
The Error I get is on the line : Windows(strSecondFile).Activate
It seems that the variable "direction" doesn't get anything, although I'm setting it as the "getdate" function.

Thank you for your help.

[GTO]

Greetings elyanivson,


Hi,
I have two open excel files.
In my procced I have to navigate between the two.


That is doubtful. Rather than activating the workbook's window, set a reference to it once, and you have better control.


Function GetDate() As String
Dim a
a = Date
Dim mystring
mystring = Format(Now(), "ddmmyyyy")
Dim currentdate As String
currentdate = "LPR" + mystring + ".xls"
End Function

The primary issue is that you never assign the built string to GetDate (see below). Also note that you use 'a' to hold today's date (which should be declared As Date), but you don't use it later.


and the sub that should activate the file is:

Sub pastedatatoIMPORT()
Dim direction
Dim strSecondFile As String
direction = GetTodayDateLPRFILENAME.GetDate
strSecondFile = "C:\Documents and Settings\yanive\Desktop\" + direction + ""
Windows(strSecondFile).Activate
End sub
The Error I get is on the line : Windows(strSecondFile).Activate
It seems that the variable "direction" doesn't get anything, although I'm setting it as the "getdate" function.

Thank you for your help.

Hopefully the part I made red is the name of a Standard Module, right?

I am not understanding what the last plus sign and trailing double quotes are to be doing?

You will want to use '&' to append strings. Try:

strSecondFile = "C:\Documents and Settings\yanive\Desktop\" & GetDate

Here's a simple demo of your function.

Sub testme()
MsgBox GetDate
End Sub

Function GetDate() As String
GetDate = "LPR" & Format(Date, "ddmmyyyy") & ".xls"
End Function

Hope that helps,

Mark

[elyanivson]

Hi Mark,
First, I want to say that your explanations are "out of this world".
Thank you very much for your patience!

now,
It partial works.
The Getdate function does work.
But, I'm still getting an error while actually trying to navigate to the other file.

The error is: Subscript out of range
at the line: Windows(strSecondFile).Activate

I can't understand what I', doing wrong.
I did exactly what you said.
strSecondFile = "C:\Documents and Settings\yanive\Desktop\" & direction

At the line after it I'm using a Msgbox to see what is the output of "direction " and it shows me exactly what I need.

Thanks again.

[elyanivson]

Hi Mark.
apparently, when I'm using Windows function I just need to input the file name and not all the direction to the file.

Thank you very much!
Problem Solved.

[GTO]

Hi Mark,
First, I want to say that your explanations are "out of this world".
Thank you very much for your patience!

No problem - what little I know, I am happy to share. Sometimes any of us become too busy, but when time allows, answering a problem helps me as well. Not only a welcomed respite from other tasks (that I probably should really be getting to...), but it helps me learn stuff I might be able to use sometime. Plus - it is obvious you are wanting to learn, which, vs. those who just ask questions to get free code/formulas, is at least for me, more personally rewarding.


now,
It partial works.
The Getdate function does work.
But, I'm still getting an error while actually trying to navigate to the other file.

The error is: Subscript out of range
at the line: Windows(strSecondFile).Activate

I can't understand what I', doing wrong.
I did exactly what you said.
strSecondFile = "C:\Documents and Settings\yanive\Desktop\" & direction

At the line after it I'm using a Msgbox to see what is the output of "direction " and it shows me exactly what I need.

First, my bad, I should have spotted that:doh:

When activating the Window, we are not looking to supply the file's FullName (the path & filename), just the workbook's name.

So, try just:
Windows(GetData).Activate

I will not belabor (I may have just miss-spelled that) this, but using Set to set a reference to the other workbook will reward your efforts.

Mark