Path file access error vba excel - Исправление ошибок и поиск оптимальных решений проблем

Павел Запивахин

Пользователь

Сообщений: 126
Регистрация: 16.08.2010

17.06.2013 07:50:04

Здравствуйте! Прошу помочь в решении возникшей проблемы. Рядом с файлом лежит папка «Бланки», в ней находятся папки с файлами (в прилагаемых макросах – «Старое имя папки» и «Файл с данными»). Необходимо на основании данных из файлов сформировать имена (Link) и присвоить их родительским папкам («Старое имя папки» заменить на Link). Первый макрос, где я конкретно прописываю FilePath, работает. Во втором макросе я пытаюсь взять путь к файлу из диалога GetOpenFilename, VBA на строке переименования выдает ошибку: Run-time error ‘75’: Path/file access error. Подскажите, пожалуйста, что делаю не так?
И еще один вопрос. Если во втором макросе переменной FilePath присвоить тип String, то на строке If FilePath = False Then появляется ошибка о несоответствии типа переменной, а тип Variant пропускает. Как правильно вычислить, что в диалоговом окне файл не был выбран?
Макрос, который работает:

Код

Sub Работает()
Dim FilePath As String
Dim FolderPath As String
Dim FolderName As String
Dim iExtension As String
Dim link As String ' новое имя папки
Dim check As Boolean
       
FilePath = "C:UsersПавелDesktopБЗБланкиСтарое имя папкиФайл с данными.doc"

iExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(FilePath) ' Расширение файла
FolderPath = CreateObject("Scripting.FileSystemObject").GetParentFolderName(FilePath) ' Имя родительской папки
FolderName = Replace(FolderPath, ThisWorkbook.Path & "" & "Бланки" & "", "") ' Имя папки для TextToDisplay гиперссылки
link = "Новое имя папки"
Name FolderPath As ThisWorkbook.Path & "Бланки" & link
End Sub

Макрос, который не работает:

Код

Sub Не_работает()
Dim FilePath As Variant
Dim FolderPath As String
Dim FolderName As String
Dim iExtension As String
Dim WordApp As Object, CopyArea As Variant
Dim link As String ' новое имя папки
Dim check As Boolean
   
ChDir ThisWorkbook.Path & "" & "Бланки" & "" ' Путь к папкам с бланками-заказами
FilePath = Application.GetOpenFilename("all Files (*.*), *.*", Title:="Выберите файл") ' Диалог выбора файла
If FilePath = False Then 'Если файл не выбран, то сбрасывается check, выводится сообщение и выход
    check = False
    MsgBox "Файл не выбран!", 48, "ВНИМАНИЕ!"
    Exit Sub
End If

iExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(FilePath) ' Расширение файла
FolderPath = CreateObject("Scripting.FileSystemObject").GetParentFolderName(FilePath) ' путь к родительской папке
FolderName = Replace(FolderPath, ThisWorkbook.Path & "" & "Бланки" & "", "") ' Имя папки для TextToDisplay гиперссылки
link = "Новое имя папки" ' в данном макросе равно FolderName
Name FolderPath As ThisWorkbook.Path & "Бланки" & link
End Sub

KuklP Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	М-да. Понял в чем вопрос. Оказывается файловые диалоги блокируют путь к папке. Пока нет времени разбираться, может позже, если никто ничего не придумает. Изменено: KuklP — 17.06.2013 09:03:30 Я сам — дурнее всякого примера! …

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

17.06.2013 09:17:40

Можно перебить другим диалогом в нем ничего не выбирая. Так работает:

Код

...
    FilePath = Application.GetOpenFilename("all Files (*.*), *.*", Title:="Выберите файл")    ' Диалог выбора файла
    If FilePath = False Then    'Если файл не выбран, то сбрасывается check, выводится сообщение и выход
        check = False
        MsgBox "Файл не выбран!", 48, "ВНИМАНИЕ!"
        Exit Sub
    End If
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = 0
        .InitialFileName = "c:*.ddd"
                .Show
        '        FilePath = .SelectedItems(1)
    End With
...

Я сам — дурнее всякого примера! …

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

17.06.2013 09:21:43

И так работает:

Код

    FilePath = Application.GetOpenFilename("all Files (*.*), *.*", Title:="Выберите файл")    ' Диалог выбора файла
    If FilePath = False Then    'Если файл не выбран, то сбрасывается check, выводится сообщение и выход
        check = False
        MsgBox "Файл не выбран!", 48, "ВНИМАНИЕ!"
        Exit Sub
    End If
    ChDir ThisWorkbook.Path & "" & "Бланки" & ""

Я сам — дурнее всякого примера! …

Павел Запивахин

Пользователь

Сообщений: 126
Регистрация: 16.08.2010

17.06.2013 09:45:29

Уважаемый KukIP, сделал так:

Код

Sub Не_работает()
Dim FilePath As String
Dim FolderPath As String
Dim FolderName As String
Dim iExtension As String
Dim WordApp As Object, CopyArea As Variant
Dim link As String ' новое имя папки
Dim check As Boolean
   
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = 0
        .InitialFileName = ThisWorkbook.Path & "" & "Бланки" & "*.*"
        .Show
        FilePath = .SelectedItems(1)
    End With

iExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(FilePath) ' Расширение файла
FolderPath = CreateObject("Scripting.FileSystemObject").GetParentFolderName(FilePath) ' Имя родительской папки
FolderName = Replace(FolderPath, ThisWorkbook.Path & "" & "Бланки" & "", "") ' Имя папки для TextToDisplay гиперссылки
link = "Новое имя папки"
Name FolderPath As ThisWorkbook.Path & "Бланки" & link
End Sub

Но все-равно на строке Name FolderPath As ThisWorkbook.Path & «Бланки» & link выдает ошибку: Run-time error ‘75’: Path/file access error.

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

17.06.2013 13:29:23

Павел Запивахин, Вы пропустили в моем посте весьма важную деталь. А именно:

Код
ChDir ThisWorkbook.Path & "" & "Бланки" & ""

после диалога выбора файла! ChDir получается, перехватывает у диалога занятость папки и освобождает ее для дальнейших манипуляций.

Изменено: KuklP — 17.06.2013 13:30:06

Я сам — дурнее всякого примера! …

	Получилось! KukIP, большое спасибо за помощь!

KuklP Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	#8 17.06.2013 14:09:20 Заходите. Мы всегда рады тем, кто сам чего-то делает Я сам — дурнее всякого примера! …

Источник

Remove From My Forums

Question
Dears,

pleas i need help as i faced Path/File Access Error when opening the file, when the debug popup window is appeared the hit debug

another crash window popped up

as per the glitch massage, i can’t identify the problem with which Code, this workbook have more than one code plus have about 4 forms. as shown i use office 2013.

if the workbook is required to preview i can upload

Gratefully,
- Moved by
  George123345
  Friday, February 20, 2015 2:05 AM

All replies

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, this issue is related to debug code, I’ll move your question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

It’s recommended to download and install
Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office
programs.
Hi Ahmed MAbdel Kader,

To determin whether the issue was caused by the code in VBA, I susggest that you try to open the workbook in safe mode.

Then you can remove the code to wether the issue was fixed. If yes, we can comment the code line by line to see which line of code to cause this issue.

Regards & Fei

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Click
HERE to participate the survey.
Hello Fie,

I did what you commented then i opened the workbook in safe mode. the error went away, and the sheet work as well, when opened it in another machine both with office 2010 or 2013, nothing go wrong, this error occurs with my machine only, while this workbook
was work perfectly without any glitch before, neither new add-ins nor software had been installed before crash for this bug, but creating Lync account only,

any suggestions for treating these trouble, is repairing or re-install office can correct the matter?, appreciate any additional suggestions

Gratefully,
- Edited by
  Ahmed M Abdel Kader
  Sunday, February 22, 2015 8:52 PM
Hi Ahmed,

Can you post the VBA code snippet that caused this problem?

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Click
HERE to participate the survey.
Hello Caillen,

as i clarified before the workbook have a lot of codes related of filter data in listbox, filter data by userform, replace data from sheet to another one, code for outlook notification with a specific action «when replace data»

i uploaded the zipped workbook, the Codes is commented as much as i can to be easy somehow, if there are some needed info, i’ll provide you.

the workbook

Gratefully,
Hello Fie,

as you clarified may be this issue only occur on the specific machine, i’ll take a look to the mentioned
URLs perhaps get it solved,

Thanks a lot,

Источник

VBForums
Visual Basic
Office Development
[RESOLVED] Path File Access Error

Dec 14th, 2007, 05:10 PM

#1

Thread Starter

Hyperactive Member

[RESOLVED] Path File Access Error

I’ve got a pretty huge project in an Excel Workbook working very nicely, except for one extremely annoying issue…

Very randomly, sometimes while running code other times if I just leave the workbook open with nothing executing for a few hours, I will get a Path File Access Error. I end up having to hit OK about 45 times, then a

&H8000FFFF (-2147418133) Catastrophic Failure occurs … crashing Excel and not being able to save anything..

Anyone know why this would occur? I thought I may have accidentally left a text document open or something of the such, but this error can occur if I simply open the workbook and execute no code (save the workbook_open where my initial form is loaded and displayed)…

Thanks much
Dec 14th, 2007, 05:12 PM

#2

Thread Starter

Hyperactive Member

Re: Path File Access Error

Also my workbook is saved on a network hard drive… but I’ve never had problems accessing/saving/etc files with it.
Dec 14th, 2007, 05:49 PM

#3

Re: Path File Access Error

Three Things that I can think of…

1) Have you checked the userform activate event?
Is there a code which is trying to access a particular path?

2) Have you checked the various events of workbook/userform wherein a code is trying to access a particular file/path?

3) Is there a piece of code somewhere which is trying to take a backup when idle somewhere which is not accessible?

A good exercise for the Heart is to bend down and help another up…
Please Mark your Thread «Resolved», if the query is solved

MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27″ 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Jan 14th, 2008, 05:55 PM

#4

Thread Starter

Hyperactive Member

Re: Path File Access Error

hmm.. everything seems to be checking out okay. I don’t have any code set to run during idle times, at least that I’m aware of.

Only thing I can think of is that sometimes I don’t unload forms, merely hiding them.. Is having a lot of hidden forms going to trip me up? or having a form hidden for a certain amount of time eventually crash Excel?

thanks
Jan 15th, 2008, 09:28 AM

#5

Re: Path File Access Error

Originally Posted by pgag45

hmm.. everything seems to be checking out okay. I don’t have any code set to run during idle times, at least that I’m aware of.

Only thing I can think of is that sometimes I don’t unload forms, merely hiding them.. Is having a lot of hidden forms going to trip me up? or having a form hidden for a certain amount of time eventually crash Excel?

thanks

Well I don’t think that hiding forms will hang excel (i could be wrong) there has to be some piece of code which is trying to access a particular path.

How big is your file?

A good exercise for the Heart is to bend down and help another up…
Please Mark your Thread «Resolved», if the query is solved

MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27″ 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Jan 21st, 2008, 12:43 PM

#6

Thread Starter

Hyperactive Member

Re: Path File Access Error

yeah and I still can’t find it for the life for of me..

My file is currently at 92mb (Ouch I know, but the images are killing me).. Although the file does zip down to ~ 3.5 mb…

Would not closing a file I opened at one point hang me up?
Jan 21st, 2008, 12:50 PM

#7

Re: Path File Access Error

Originally Posted by pgag45

yeah and I still can’t find it for the life for of me..

My file is currently at 92mb (Ouch I know, but the images are killing me).. Although the file does zip down to ~ 3.5 mb…

Would not closing a file I opened at one point hang me up?

Till you reduce the file, I cannot even tell you to upload the file

Lets hope that after you reduce the file size (as mentioned in the other thread), we could have a look at your file…

A good exercise for the Heart is to bend down and help another up…
Please Mark your Thread «Resolved», if the query is solved

MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27″ 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Jan 21st, 2008, 01:52 PM

#8

Re: Path File Access Error

I would debug your workbook by commenting out all the code that may be accessing the file system. Save it and keep running. If no errors then uncomment some code and save. Repeat until you start getting crashes again. Then you will find the code, if any, that is making your workbook puke.

VB/Office Guru� (AKA: Gangsta Yoda� �)
I dont answer coding questions via PM. Please post a thread in the appropriate forum. Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
� Reps & Rating Posts � VS.NET on Vista � Multiple .NET Framework Versions � Office Primary Interop Assemblies � VB/Office Guru� Word SpellChecker�.NET � VB/Office Guru� Word SpellChecker� VB6 � VB.NET Attributes Ex. � Outlook Global Address List � API Viewer utility � .NET API Viewer Utility �
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24″ LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
Jan 22nd, 2008, 04:26 AM

#9

Re: Path File Access Error

Also my workbook is saved on a network hard drive… but I’ve never had problems accessing/saving/etc files with it.

it maybe loosing connection to the network drive while the machine is idle, or some other occurrence
if you can, make a copy on a local drive to test if the problem still occurs

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete
Jan 22nd, 2008, 04:24 PM

#10

Thread Starter

Hyperactive Member

Re: Path File Access Error

Yeah I’m really starting to think it has to do with the network drive.. I’m going to begin working on the local drive and see if it occurs now

thanks for the help
Jan 23rd, 2008, 05:33 PM

#11

Thread Starter

Hyperactive Member

Re: Path File Access Error

I’m going to declare this a success (hopefully don’t jinx myself haha)… Moving the file onto a local drive seems to have fixed the problem… I think it may have been trying to autosave onto the network drive, which apparently wouldn’t be available causing the crash.. anyway, thanks all for the help!