We help IT Professionals succeed at work.

Problem to line

215 Views
Last Modified: 2018-03-02
Hi,
I've got error
21e.png
due to last line below
    File0 = Replace(Replace(ActiveWorkbook.Path & "\..\Index\" & File1 & ".txt", "/", "\"), "http:", "")
    Debug.Print File0
    Open File0 For Output As #1

Open in new window


and could not capture value of File0 in Watch window. Why?
Comment
Watch Question

try the code below

    File0 = Replace(Replace(ActiveWorkbook.Path & "\Index\" & File1 & ".txt", "/", "\"), "http:", "")
    Debug.Print File0
    Open File0 For Output As #1

Open in new window

Peter ChanProblem resolver

Author

Commented:
I tried with your codes and still encountered the same problem.

The relevant path is correct.
"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Peter ChanProblem resolver

Author

Commented:
I cannot show

Debug.Print File0

in Watch window. What to adjust?
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
In Watch window you only type File0, no command.
Peter ChanProblem resolver

Author

Commented:
I want to see value of File0 but I get nothing.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
To use Watch window, you need to set a breakpoint e.g. at the debug.print line.
Peter ChanProblem resolver

Author

Commented:
Debug.Print should still show the value of File0, even if there is no break point, right?
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Yes. BUt you need to have the Output window (or whatever it is called) opened to see.
Peter ChanProblem resolver

Author

Commented:
I want to refer to current path of Excel file (Within Sharepoint). It seems ActiveWorkbook.Path is not revealing the correct path of current Excel file.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Peter ChanProblem resolver

Author

Commented:
I get
21j.png
due to last line below

    File0 = Replace(Replace(Application.ActiveWorkbook.Path & "\..\Index\" & File1 & ".txt", "/", "\"), "http:", "")

    'File0 = ActiveWorkbook.Path & "\..\Index\" & File1 & ".txt"
    Debug.Print Application.ActiveWorkbook.Path
    Debug.Print File0
    Open File0 For Output As #1

Open in new window


it seems Application.ActiveWorkbook.Path is not revealing the correct path of current Excel file being opened.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
We might be able to help if you provide example output of the debug print above, as I don't have a clue how SharePoint provides its files.
Peter ChanProblem resolver

Author

Commented:
Here are Debug values I've got

\\hfactx.corp.zim.com\ctx$\UPM.FR\HKGDesktop
\\hfactx.corp.zim.com\ctx$\UPM.FR\HKGDesktop\..\Index\JAPAN.txt

but actually current Excel file is residing in the following path
21m.png
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
I can't see the real path in the screenshot.
Peter ChanProblem resolver

Author

Commented:
Here is the relevant path
21n.png
Problem is that Application.ActiveWorkbook.Path is not showing correct path as expected.
Peter ChanProblem resolver

Author

Commented:
Any help?
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Do you have more than one workbook open, so that the active one is not the one that you are expecting?
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
In your last snippet, what is the type and value of Window0?

Is it a file name or an index number?
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
What's the solution?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.