DIR command returns Bad file name or number error

**longandtallpaul** · 10-18-2018, 03:34 PM

Hi,

I have a few lines of code which check to see if a file exists on our sharepoint and then if it doesn't it gives a message and closes the macro or if it does exist it opens the file. I get a run time error on the DIR part of the code. The workbooks.open code works fine on its own so the file path is ok. The DIR part of the code does work if I first go into the sharepoint and open the folder, then the code works fine. Then if I close excel and re open the code doesn't work again. Is there an alternative I can use instead of DIR to find out if a file exists or another idea? I've replaced the actual sharepoint file path with the words sharepoint address and folders etc

Please Login or Register  to view this content.

**6StringJazzer** · 10-18-2018, 04:27 PM

I do not have experience in doing this with SharePoint files but if your Open function works then try this.

Please Login or Register  to view this content.

By the way, your code is suspicious because of the capitalization. It is better to copy and paste your code into a post rather than type it in.

**longandtallpaul** · 10-18-2018, 04:41 PM

Thanks, I had to type it because I'm using my home pc to post on the forum as cant access from my work laptop and we are not allowed to email to our personal email from work email. All quite long winded and frustrating!

Does the on error cover just the part of the macro which is trying to open the file or all of the page, my macro was quite long and if something else fails don't want to all join in

or could I separate this part into its own sub I guess.

Just tried your code, the problem is excel gives an error window which says "we cant connect to //etc etc.xlsx. please make sure you're using the correct web addess" this is a message which once you then click ok, the file not on sharepoint message box than comes up. Is there a way to disable the excel warning message before? If there is then this is perfect

**longandtallpaul** · 10-18-2018, 04:56 PM

Just to add to this as well, the DIR code does work if I go into the folder on the sharepoint manually first before running the DIR code.

**Zer0Cool** · 10-18-2018, 04:58 PM

I may be wrong here but I am not sure DIR works with external links to files.

If it does I imagine you need to login to SharePoint to access these files? If so DIR doesn't pass credentials and likely doesn't have permissions as a guest/unknown/anonymous "user" to get to the file.

It also occurs to me you may need to replace certain characters with their HTML representation in the URL. For example space is %20.

A quick google search suggests there may be alternate ways to do this which may provide you with more options for how you pass info like credentials and how you return info. Here is an example using MSXML2.XMLHTTP.6.0 objects instead of DIR or FSO: https://social.technet.microsoft.com...repointgeneral

**longandtallpaul** · 10-18-2018, 05:03 PM

Thanks, its an internal sharepoint so we're always signed in. For the URL, the path works with the Open code, just not with the DIR, although I have experimented all afternoon with adding %20's %2R's / \ and every combination

I think the solution to use on error will work, just need to get rid of the excel warning message now that pops up

**6StringJazzer** · 10-18-2018, 05:41 PM

Originally Posted by longandtallpaul

Does the on error cover just the part of the macro which is trying to open the file or all of the page, my macro was quite long and if something else fails don't want to all join in

or could I separate this part into its own sub I guess.

I assumed this code was your whole sub. If not we need to start again. Comments in green, additional code in red.

Please Login or Register  to view this content.

Just tried your code, the problem is excel gives an error window which says "we cant connect to //etc etc.xlsx. please make sure you're using the correct web addess" this is a message which once you then click ok, the file not on sharepoint message box than comes up. Is there a way to disable the excel warning message before? If there is then this is perfect

I don't know where that message is coming from. I do have access to SharePoint, just never tried to do anything like this. I'll set up a test and what I can figure out.

DIR command returns Bad file name or number error

LinkBack

Thread Tools

Rate This Thread

Display

DIR command returns Bad file name or number error

Re: DIR command returns Bad file name or number error

Re: DIR command returns Bad file name or number error

Re: DIR command returns Bad file name or number error

Re: DIR command returns Bad file name or number error

Re: DIR command returns Bad file name or number error

Re: DIR command returns Bad file name or number error

Thread Information

Users Browsing this Thread

Similar Threads

Kill command & Path/File access error (Error 75)

Excel 2013-Resaving a document using Close icon returns error File Not found

WorksheetFunction.Match returns error with String variable, but not with number

Run time error '-2147217908(80040e0c)': command text was not set for the command object

[SOLVED] How to stop error when I cancel the input file source command?

returns covariance MMULT command

Kill Command - file not found error

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions