ChDir pull file path

mysticmario

Active Member
Joined
Nov 10, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
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?
VBA Code:
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
 
Ok the issue has to be somewhere deeper. I reverted to the old code
VBA Code:
    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

It also doesnt work, the same publish issue
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Ok the issue has to be somewhere deeper. I reverted to the old code
VBA Code:
    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

It also doesnt work, the same publish issue
so what has changed elsewhere in the macros? if youre old code no longer works.
 
Upvote 0
This is insane.
For testign purposes I recorded a macro where I manually publish the sheet as html and it worked.
Copied the same code to the publish() routine and it doesn't work I get the same publish error as always.
I'm mezmerized
 
Upvote 0
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?
VBA Code:
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
on top of that when i try to us ethe same macro again it's doesnt work, what the f...
 
Upvote 0
have y
on top of that when i try to us ethe same macro again it's doesnt work, what the f...
ou tried publishing to a different location to see if that changes anything? at least would rule out any access/filepath issues
 
Upvote 0
in case theres some delay in accessing the remote drive ( i think you said you were running direct on a PC drive before) could you try adding the line

Application.Wait(Now + TimeValue("0:00:10")) Before the .publish (false) line.
 
Upvote 0
I tried eveyrthing and simply doesnt work.
I record macro it works, i rerun macor it works I call it from a button it doesnt work, I try to rerun the macro from macro list and it doesnt work anymore.
I dleted the button created new active X button, same problem
 
Upvote 0
I create form control button and assigned macro - it worksI then created new activeX button assigned sub to it and i didnt work in addition previously assigned form control button macro also stopped working.
The issue is with the activeX control but I use them pretty much exclusively...

@edit
So i deleted the activeX button and the form control button started working again...
@edit2
Every code works now, my old code, new code created by you, i just can't use activeX button for that
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top