Identifying drive letter with Save-as to a server

I posted this in a thread of mine earlier. Maybe it will get buried
there.... But, perhaps it will benefit others to see the Q & A for
this problem, as I'm sure others will face a similiar situation. So, I
make a new post here.....


I have a twist in this project I just discovered this afternoon.
This file resides on a server. When I ran the macro, which includes the

Save-as code shown below, it worked fine. However, I found out I need
to
specify the path.

ActiveWorkbook.SaveAs _
Filename:="Staging List " & Format(Worksheets("Official
List").Range("Current_Date").Value, "mm-dd-yy") & ".xls"
FileFormat:=xlWorkbookNormal

When the person who does the entries each day ran it, the file saved on

his hard drive. It seems that it went to the last place he pulled a
file from, even though it was a different file, which was from his hard

drive. This baffled me, since anytime I've ever run a Save-as from any
program, I thought it always assumes the directory it came from.

Well, I guess I should learn good form, and put the entire path in
anyway.

So, I figured out how to show the path as far as the directories go.
But, I'll probably need to show the drive letter also. The problem is
that on different computers in our company, the drive letter can be
different to the same server drive. On my pc, this drive letter is F
On another person's, it's H, and who knows what other letters are being

used to access this same server.

Here is what I have so far:

ActiveWorkbook.SaveAs _
Filename:="\Material Staging List\Staging List " &
Format(Worksheets("Official List").Range("Current_Date").Value,
"mm-dd-yy") & ".xls"
FileFormat:=xlWorkbookNormal

The server drive is called Public.
So, I assume it would look like
Filename:="\Public\Material Staging List\Staging List " & ........
Question is: Will I need to identify the drive letter? I would think
so.

In the long run, I want different people assigned to update this file
using the macros I'm designing. How do I get around the drive letter
differentiation from pc to pc?


As always, my humble appreciation to all who take the time to respond.
J.O.

Just an untried guess. Can you use \\servername\path\filename?

--
Ian
--
"excelnut1954" <[email protected]> wrote in message
news:[email protected]...
>I posted this in a thread of mine earlier. Maybe it will get buried
> there.... But, perhaps it will benefit others to see the Q & A for
> this problem, as I'm sure others will face a similiar situation. So, I
> make a new post here.....
>
>
> I have a twist in this project I just discovered this afternoon.
> This file resides on a server. When I ran the macro, which includes the
>
> Save-as code shown below, it worked fine. However, I found out I need
> to
> specify the path.
>
> ActiveWorkbook.SaveAs _
> Filename:="Staging List " & Format(Worksheets("Official
> List").Range("Current_Date").Value, "mm-dd-yy") & ".xls"
> FileFormat:=xlWorkbookNormal
>
> When the person who does the entries each day ran it, the file saved on
>
> his hard drive. It seems that it went to the last place he pulled a
> file from, even though it was a different file, which was from his hard
>
> drive. This baffled me, since anytime I've ever run a Save-as from any
> program, I thought it always assumes the directory it came from.
>
> Well, I guess I should learn good form, and put the entire path in
> anyway.
>
> So, I figured out how to show the path as far as the directories go.
> But, I'll probably need to show the drive letter also. The problem is
> that on different computers in our company, the drive letter can be
> different to the same server drive. On my pc, this drive letter is F
> On another person's, it's H, and who knows what other letters are being
>
> used to access this same server.
>
> Here is what I have so far:
>
> ActiveWorkbook.SaveAs _
> Filename:="\Material Staging List\Staging List " &
> Format(Worksheets("Official List").Range("Current_Date").Value,
> "mm-dd-yy") & ".xls"
> FileFormat:=xlWorkbookNormal
>
> The server drive is called Public.
> So, I assume it would look like
> Filename:="\Public\Material Staging List\Staging List " & ........
> Question is: Will I need to identify the drive letter? I would think
> so.
>
> In the long run, I want different people assigned to update this file
> using the macros I'm designing. How do I get around the drive letter
> differentiation from pc to pc?
>
>
> As always, my humble appreciation to all who take the time to respond.
> J.O.
>

And you did have another reply to that followup.

excelnut1954 wrote:
>
> I posted this in a thread of mine earlier. Maybe it will get buried
> there.... But, perhaps it will benefit others to see the Q & A for
> this problem, as I'm sure others will face a similiar situation. So, I
> make a new post here.....
>
> I have a twist in this project I just discovered this afternoon.
> This file resides on a server. When I ran the macro, which includes the
>
> Save-as code shown below, it worked fine. However, I found out I need
> to
> specify the path.
>
> ActiveWorkbook.SaveAs _
> Filename:="Staging List " & Format(Worksheets("Official
> List").Range("Current_Date").Value, "mm-dd-yy") & ".xls"
> FileFormat:=xlWorkbookNormal
>
> When the person who does the entries each day ran it, the file saved on
>
> his hard drive. It seems that it went to the last place he pulled a
> file from, even though it was a different file, which was from his hard
>
> drive. This baffled me, since anytime I've ever run a Save-as from any
> program, I thought it always assumes the directory it came from.
>
> Well, I guess I should learn good form, and put the entire path in
> anyway.
>
> So, I figured out how to show the path as far as the directories go.
> But, I'll probably need to show the drive letter also. The problem is
> that on different computers in our company, the drive letter can be
> different to the same server drive. On my pc, this drive letter is F
> On another person's, it's H, and who knows what other letters are being
>
> used to access this same server.
>
> Here is what I have so far:
>
> ActiveWorkbook.SaveAs _
> Filename:="\Material Staging List\Staging List " &
> Format(Worksheets("Official List").Range("Current_Date").Value,
> "mm-dd-yy") & ".xls"
> FileFormat:=xlWorkbookNormal
>
> The server drive is called Public.
> So, I assume it would look like
> Filename:="\Public\Material Staging List\Staging List " & ........
> Question is: Will I need to identify the drive letter? I would think
> so.
>
> In the long run, I want different people assigned to update this file
> using the macros I'm designing. How do I get around the drive letter
> differentiation from pc to pc?
>
> As always, my humble appreciation to all who take the time to respond.
> J.O.

--

Dave Peterson

>>I posted this in a thread of mine earlier

I found it. You got some advice and seem to have used it, but you didn't
respond to it. You know, people who help here do like to hear how it worked
out.

re your latest need, it you want to save to the same path as the workbook
was opened from wouldn't:

ActiveWorkbook.SaveAs Filename:= Activeworkbook.Path & "\" &
Format(Worksheets("Official
List").Range("Current_Date").Value, "mm-dd-yy") & ".xls"

do it?

--
Jim
"excelnut1954" <[email protected]> wrote in message
news:[email protected]...
|I posted this in a thread of mine earlier. Maybe it will get buried
| there.... But, perhaps it will benefit others to see the Q & A for
| this problem, as I'm sure others will face a similiar situation. So, I
| make a new post here.....
|
|
| I have a twist in this project I just discovered this afternoon.
| This file resides on a server. When I ran the macro, which includes the
|
| Save-as code shown below, it worked fine. However, I found out I need
| to
| specify the path.
|
| ActiveWorkbook.SaveAs _
| Filename:="Staging List " & Format(Worksheets("Official
| List").Range("Current_Date").Value, "mm-dd-yy") & ".xls"
| FileFormat:=xlWorkbookNormal
|
| When the person who does the entries each day ran it, the file saved on
|
| his hard drive. It seems that it went to the last place he pulled a
| file from, even though it was a different file, which was from his hard
|
| drive. This baffled me, since anytime I've ever run a Save-as from any
| program, I thought it always assumes the directory it came from.
|
| Well, I guess I should learn good form, and put the entire path in
| anyway.
|
| So, I figured out how to show the path as far as the directories go.
| But, I'll probably need to show the drive letter also. The problem is
| that on different computers in our company, the drive letter can be
| different to the same server drive. On my pc, this drive letter is F
| On another person's, it's H, and who knows what other letters are being
|
| used to access this same server.
|
| Here is what I have so far:
|
| ActiveWorkbook.SaveAs _
| Filename:="\Material Staging List\Staging List " &
| Format(Worksheets("Official List").Range("Current_Date").Value,
| "mm-dd-yy") & ".xls"
| FileFormat:=xlWorkbookNormal
|
| The server drive is called Public.
| So, I assume it would look like
| Filename:="\Public\Material Staging List\Staging List " & ........
| Question is: Will I need to identify the drive letter? I would think
| so.
|
| In the long run, I want different people assigned to update this file
| using the macros I'm designing. How do I get around the drive letter
| differentiation from pc to pc?
|
|
| As always, my humble appreciation to all who take the time to respond.
| J.O.
|

Yes I did get responses there.
Sorry for the confusion. After posting there, I thought I should post a
new topic for this problem. It WAS a different problem in a way.
Anyway...... I got a solution that worked from Tom Ogilvy. It was
simple. As I replied there, there might be solutions offered, such as
with use of the UCP. I'll have to learn about that stuff later... along
with alot of other material...

Thanks,
J.O.