HOW TO CHANGE DRIVE ADDRESS IN MACRO

[K]

Hi, the macro below works perfectly fine for me but I have little
problem that file address in macro got drive F: as I am working on F:
drive on my pc but as my file will be used by other colleagues and
they are on different drives like Y: , Z: etc because we working on
network. So is it possible that where it say F:\ in macro i can put
Range("A1").value and in cell A1 i can put like drop down which have
A: to Z: drive listing so if any body is on different drive then they
can just change their dive character in cell A1. I tried doing like
this but its not working. can any body have any suggestion that what
should i do.

MACRO
===============>
Sub SAV()
Range("A13:L58").Select
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
F:\TRANSFERS & VIREMENTS RECORD\ & Range("A13").Value & " (" &
Range("K16").Value & ")" & " - " & Range("K13").Value & ".htm", _
ActiveSheet.Name, "$A$13:$L$58", xlHtmlStatic, , "")
..Publish (True)
..AutoRepublish = False
End With
Range("B27").Select
End Sub
<================

 

[Rick Rothstein \(MVP - VB\)]

Something like this maybe?

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, Range("A1").Value & _
"\TRANSFERS" & "VIREMENTS RECORD\" & Range("A13").Value & " (" & _
Range("K16").Value & ")" & " - " & Range("K13").Value & ".htm", _
ActiveSheet.Name, "$A$13:$L$58", xlHtmlStatic, , "")

Note that, as written, you code line is not correct (you are missing quote
marks around some of your String constants), so I patched it the best I
could, but I **think** you are missing a backslash (can't tell for sure)
between the "\TRANSFERS" and "VIREMENTS RECORD\" parts. In any case (whether
you are missing one or not), you can do away with concatenating them and
simply place them in the same String constant. For future postings you may
do, please copy/paste your actual code lines instead of re-typing them... it
is hard to tell whether the mistyping's are the source of your problem or
not.

Rick

 

[Ray]

The best way is not to use the letter at all but to hard-code the full
network path. So, instead of
F:\TRANSFERS & VIREMENTS RECORD\ ...
make it
\\server\folder\folder\folder\TRANSFERS & VIREMENTS RECORD\ ...

The full path should be available in the My Network Places section of
Windows Explorer ...

//ray

 

[K]

The best way is not to use the letter at all but to hard-code the full
network path.  So, instead of
F:\TRANSFERS & VIREMENTS RECORD\ ...
make it
\\server\folder\folder\folder\TRANSFERS & VIREMENTS RECORD\ ...

The full path should be available in the My Network Places section of
Windows Explorer ...

//ray

Thanks lot Rick and Ray. Your codes were fantastic.