Avatar of Peter Chan
Peter ChanFlag for Hong Kong asked on

Problem to line

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?
MiscellaneousVBAProgramming Languages-OtherMicrosoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Qlemo

8/22/2022 - Mon
Peter Kiprop

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

ASKER
Peter Chan

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

The relevant path is correct.
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Peter Chan

I cannot show

Debug.Print File0

in Watch window. What to adjust?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Qlemo

In Watch window you only type File0, no command.
ASKER
Peter Chan

I want to see value of File0 but I get nothing.
Qlemo

To use Watch window, you need to set a breakpoint e.g. at the debug.print line.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Peter Chan

Debug.Print should still show the value of File0, even if there is no break point, right?
Qlemo

Yes. BUt you need to have the Output window (or whatever it is called) opened to see.
ASKER
Peter Chan

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Peter Chan

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

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.
ASKER
Peter Chan

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

I can't see the real path in the screenshot.
ASKER
Peter Chan

Here is the relevant path
21n.png
Problem is that Application.ActiveWorkbook.Path is not showing correct path as expected.
ASKER
Peter Chan

Any help?
Your help has saved me hundreds of hours of internet surfing.
fblack61
GrahamSkan

Do you have more than one workbook open, so that the active one is not the one that you are expecting?
GrahamSkan

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

Is it a file name or an index number?
Qlemo

What's the solution?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.