Code Problem

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
I have a problem with the following code

I changed (On both Occurances)

ChDir "'\\hqnas01p\cac$\Management\Customer Relations\Complaint Log"

from

ChDir "'Z:\Management\Customer Relations\Complaint Log"

So I could dial straight into the server, over using a filepath & this wont work now, I could change it back - but it has all sorts of complications with certain people not having certain drives mapped to their profile

Any idea's Guys?

TIA

Code:
Sub Open_Complaint_Log()

'Script which opens the complaint log from its network location
'Link changed to CAC S: MA

Application.ScreenUpdating = False
ChDir "'\\hqnas01p\cac$\Management\Customer Relations\Complaint Log"
    Workbooks.Open Filename:= _
        "'\\hqnas01p\cac$\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"
Application.ScreenUpdating = True

End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Mark

I think ChDir only works with mapped drive letters (not 100% sure about this). Why do you need to use ChDir anyway?

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Mark

I think ChDir only works with mapped drive letters (not 100% sure about this). Why do you need to use ChDir anyway?

Richard

I thought this was the only way to open a File Richard?

Is there another?

(PS Sorry for the delay, meetings an all that!)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Mark

You're already giving the Workbooks statement the full path & name, so amending the ChDir doesn't actually do anything in this instance.

You should be able to safely comment the ChDir statement out.

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

Mark

You're already giving the Workbooks statement the full path & name, so amending the ChDir doesn't actually do anything in this instance.

You should be able to safely comment the ChDir statement out.

Richard

It doesn't like it without the ChDir?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Mark

In your code, you appear to have a rogue single quote in the Workbooks.Open line, can you take it out and see if it makes a difference.

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

Mark

In your code, you appear to have a rogue single quote in the Workbooks.Open line, can you take it out and see if it makes a difference.

Richard

Rogue Signal? I dont follow you Richard?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
The following has a single quote at the beginning of the file&Pathname:

Code:
Workbooks.Open Filename:= _ 
        "'\\hqnas01p\cac$\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"

Richard
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
The following has a single quote at the beginning of the file&Pathname:

Code:
Workbooks.Open Filename:= _ 
        "'\\hqnas01p\cac$\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"

Richard

I tried without Richard & it still wont have it?

Also - I have a problem here too

Excel.jpg
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Code for above picture

Code:
Private Sub workbook_open()

Application.ScreenUpdating = False
Application.StatusBar = "Development & Design by Mark Andrews"
Sheets("Criteria Selection & Pivots").Select
MsgBox "Welcome " & Application.UserName
MsgBox ("To recalculate this document, please change the dates in cells A2 & B2, then click 'Refresh Entire Document'")
Range("A2").Select
With Sheets("Productivity")
    .Cells(1, 3) = Format(Date, "mmmm")
    '.Cells(1, 4) = "=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))"
    .Cells(1, 4) = Evaluate("=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))")
    End With
Application.ScreenUpdating = True

End Sub
 

Forum statistics

Threads
1,136,926
Messages
5,678,610
Members
419,776
Latest member
mikelowski

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
Top