ChDir pull file path

[mysticmario]

Hi, Whenever i make changes to certain sheet in my excel I have an auto publish macro which creates html file out of it.
It works great cause the directory I Have put is taken from my PC. However, different people use this file from different PCs and despite this file being on google drive everyone has different path to it how can I make this Publish() sub to get its ChDir using the path it was opened from?

Sub publish()
    ChDir "G:\Mój dysk\ArtProInfo v1.5_Shared"
    With ActiveWorkbook.PublishObjects("ArtProInfo v1.5_8106")
        .FileName = "G:\Mój dysk\ArtProInfo v1.5_Shared\index.html"
        .publish (False)
        .AutoRepublish = False
    End With
End Sub

 

[mysticmario]

again i think you missing the "=" sign before false

bue there never was = sign in this code previously and it worked

 

[mysticmario]

again i think you missing the "=" sign before false

nope.

 

[gordsky]

what happens if you change with activeworkbook to thisworkbook

 

[mysticmario]

what happens if you change with activeworkbook to thisworkbook

 

[gordsky]

is your code in a button click event or a module

 

[gordsky]

is your code in a button click event or a module

having tested the code i think it is because the focus is shifted away from the sheet you want to publish prior to the .publish command. Insert a line before to activate the sheet you are publishing. thisworkbook.sheets(yoursheetname).activate.
This will be as I believe the button you are using to publish is an active x button rather than a form control button.

 

[mysticmario]

having tested the code i think it is because the focus is shifted away from the sheet you want to publish prior to the .publish command. Insert a line before to activate the sheet you are publishing. thisworkbook.sheets(yoursheetname).activate.
This will be as I believe the button you are using to publish is an active x button rather than a form control button.

active x Button calls publish sub yes, but in other places the code is called from another sub
When someone creates a new project it automatically updates the html which is then displayed on tablet. Quite complex project
I woluldn't really want to activate the sheet in other instances where another code is calling it its quite inconvenient.
this code also uses this sub:

Private Sub nowy_Click()
Dim nazwaProjektu As String, adresProjektu As String, terminProjektu As String
Dim lr As Long
Dim number As Single

number = Int(Rnd() * (99999 - 10000 + 1)) + 10000


nazwaProjektu = InputBox("Podaj nazwę projektu", "Stwórz nowy projekt")
adresProjektu = InputBox("Podaj adres", "Stwórz nowy projekt")
terminProjektu = InputBox("Podaj ostateczną datę zakończenia projektu(DDMMRRRR)", "Stwórz nowy projekt")
Sheets("OTWARTE PROJEKTY").Unprotect
If Not nazwaProjektu = Empty Then
lr = Sheets("OTWARTE PROJEKTY").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("OTWARTE PROJEKTY").Cells(lr + 1, "B").Value = number & " " & nazwaProjektu
lr = Sheets("OTWARTE PROJEKTY").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("OTWARTE PROJEKTY").Cells(lr + 1, "A").Value = Date
    If Not adresProjektu = Empty Then
    lr = Sheets("OTWARTE PROJEKTY").Cells(Rows.Count, "C").End(xlUp).Row
    Sheets("OTWARTE PROJEKTY").Cells(lr + 1, "C").Value = adresProjektu
    Else
    adresProjektu = "--"
    Sheets("OTWARTE PROJEKTY").Cells(lr + 1, "C").Value = adresProjektu
    End If
End If
lr = Sheets("OTWARTE PROJEKTY").Cells(Rows.Count, "D").End(xlUp).Row
If terminProjektu = Empty Then
Sheets("OTWARTE PROJEKTY").Cells(lr + 1, "D").Value = "N/D"
Else
Sheets("OTWARTE PROJEKTY").Cells(lr + 1, "D").Value = Format(terminProjektu, "DD.MM.YYY")
End If
    Range("A3:A500").Select
    ActiveWorkbook.Worksheets("OTWARTE PROJEKTY").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("OTWARTE PROJEKTY").sort.SortFields.Add2 Key:=Range _
        '("A3:A200"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        'xlSortNormal
    With ActiveWorkbook.Worksheets("OTWARTE PROJEKTY").sort
        .SetRange Range("A2:E500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Call publish
Sheets("OTWARTE PROJEKTY").Protect
NowyProjekt.Show

 

[mysticmario]

Sub publish()
ThisWorkbook.Sheets("OTWARTE PROJEKTY").Activate
    ChDir (Application.ThisWorkbook.Path)
    With ThisWorkbook.PublishObjects("ArtProInfo v1.5_8106")
        .FileName = Application.ThisWorkbook.Path & "\" & "index.html*"
        .publish (False)
        .AutoRepublish = False
    End With
End Sub

still no go :/

 

[gordsky]

Sub publish()
ThisWorkbook.Sheets("OTWARTE PROJEKTY").Activate
    ChDir (Application.ThisWorkbook.Path)
    With ThisWorkbook.PublishObjects("ArtProInfo v1.5_8106")
        .FileName = Application.ThisWorkbook.Path & "\" & "index.html*"
        .publish (False)
        .AutoRepublish = False
    End With
End Sub

still no go :/

youve put it in the worng place try this

Sub publish()
    ChDir (Application.ThisWorkbook.Path)
    With ThisWorkbook.PublishObjects("ArtProInfo v1.5_8106")
        .FileName = Application.ThisWorkbook.Path & "\" & "index.html*"
         ThisWorkbook.Sheets("OTWARTE PROJEKTY").Activate
        .publish (False)
        .AutoRepublish = False
    End With
End Sub

 

[mysticmario]

youve put it in the worng place try this

Sub publish()
    ChDir (Application.ThisWorkbook.Path)
    With ThisWorkbook.PublishObjects("ArtProInfo v1.5_8106")
        .FileName = Application.ThisWorkbook.Path & "\" & "index.html*"
         ThisWorkbook.Sheets("OTWARTE PROJEKTY").Activate
        .publish (False)
        .AutoRepublish = False
    End With
End Sub

still nothing