The code for this is below.
I simply want to set the variable MyPath as the directory that th
workbook was opened from.

I'm coding this macro in VB

Thanks in advance for the help.


Code
-------------------

Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Dim rng As Range
Dim stng As Long
stng = 3

MsgBox "Please Wait, as this can take some time", vbOKOnly + vbCritical

SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path

ChDir MyPath
Set rng = Range("A1:IV1")
rng.ClearContents
Set rng = Range("A3:IV5005")

-------------------

--
Zlor
-----------------------------------------------------------------------
Zlord's Profile: http://www.excelforum.com/member.php...fo&userid=2751
View this thread: http://www.excelforum.com/showthread.php?threadid=47032

Dim myPath as string
mypath = thisworkbook.path

Hey, you've already got that in your code!

What's the real problem?

If that path is a different drive, maybe:

ChDrive MyPath
ChDir MyPath

(One to change drive and one to change folders.)



Zlord wrote:

The code for this is below.
I simply want to set the variable MyPath as the directory that the
workbook was opened from.

I'm coding this macro in VB

Thanks in advance for the help.

Code:
--------------------

Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Dim rng As Range
Dim stng As Long
stng = 3

MsgBox "Please Wait, as this can take some time", vbOKOnly + vbCritical

SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path

ChDir MyPath
Set rng = Range("A1:IV1")
rng.ClearContents
Set rng = Range("A3:IV5005")

--------------------

--
Zlord
------------------------------------------------------------------------
Zlord's Profile: http://www.excelforum.com/member.php...o&userid=27519
View this thread: http://www.excelforum.com/showthread...hreadid=470325

--

Dave Peterson

No matter what I put as MyPath; unless it's a straight path

Code:
--------------------

Mypath = "C:\PATH"

--------------------


Then it goes to the My Documents folder, and part of the problem is
that sometimes I have this file saved under a shared network spot

I.E.

\\serverName\asdf\asdf\asdf\File.xls

So the chdrive function doesn't work.

also in some of the code below what I had posted, I already have the

chdir MyPath script written.


--
Zlord
------------------------------------------------------------------------
Zlord's Profile: http://www.excelforum.com/member.php...o&userid=27519
View this thread: http://www.excelforum.com/showthread...hreadid=470325

The chdrive won't work on network drives.

But you can use an API call:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme02()

Dim mySavedPath As String

mySavedPath = CurDir

ChDirNet ThisWorkbook.Path

'do your stuff

ChDirNet mySavedPath

End Sub

This actually works with mapped drives, too.

Zlord wrote:

No matter what I put as MyPath; unless it's a straight path

Code:
--------------------

Mypath = "C:\PATH"

--------------------

Then it goes to the My Documents folder, and part of the problem is
that sometimes I have this file saved under a shared network spot

I.E.

\\serverName\asdf\asdf\asdf\File.xls

So the chdrive function doesn't work.

also in some of the code below what I had posted, I already have the

chdir MyPath script written.

--
Zlord
------------------------------------------------------------------------
Zlord's Profile: http://www.excelforum.com/member.php...o&userid=27519
View this thread: http://www.excelforum.com/showthread...hreadid=470325

--

Dave Peterson