View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\)[_2047_] Rick Rothstein \(MVP - VB\)[_2047_] is offline
external usenet poster
 
Posts: 1
Default HOW TO CHANGE DRIVE ADDRESS IN MACRO

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


"K" wrote in message
...
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
<================