problems downloading file w/ long name

georgec32

New Member
Joined
Mar 19, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi all. I use Office 2016 on a Windows 10 platform. I have a rather complicated VBA code to download, parse, etc some files from a professional group. I ran into a problem when trying to download one particular file with a long name. My code downloads 5 files successfully (I've been doing this for a few years) but there is one file that is unique and has a very long filename. Could the length of the filename be the problem? I run through a loop with the same code for each file. I've put in breakpoints & msgboxes to verify that the file name & the directory is correct. If I use the file path given by my code, the file downloads properly, but not with the VBA code. I'm totally stumped. Thanks in advance for any guidance on this one!

This works with file A and B but not with file C. I checked the sub-directory and there is no file C:
tgt_path = cur_path & "\" & "R_temp.zip"
URLDownloadToFile 0, doc_link, tgt_path, 0, 0

where doc_link = web_addy & "/Tdoclist/" & Tdoc_file .. web_addy is the same for all files (I verified this manually for each doc)

doc_link is
Tdoc_file (File A name): TDoc_List_Meeting_RAN#86_200602.zip
Tdoc_file (File B name): TDoc_List_Meeting_RAN#87e_200602.zip
Tdoc_file (File C name:) tdocList_3GU_format_MMexport_2020-07-05_23h45.zip

The part of the code that's not working for this one file is below:
______________________________________________________________________________________________________________
Range("A:B").Select
Selection.Find(What:="*oc*ist*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Tdoc_file = Trim(ActiveCell.Text)

'Stupid M$ VBA makes you replace the pound sign with "%23" (the character number for the pound sign) to download
Tdoc_file = Replace(Tdoc_file, "#", "%23")
doc_link = web_addy & "/Tdoclist/" & Tdoc_file
MsgBox (doc_link)
'now download the Tdoc file
tgt_path = cur_path & "\" & "R_temp.zip"
URLDownloadToFile 0, doc_link, tgt_path, 0, 0

Dim tgt_dir As String
tgt_dir = cur_path & "\"
zipped_file = tgt_path
zipped_dir = tgt_dir

Call UnziFile(zipped_file, zipped_dir)
______________________________________________________________________________________________________________

georgec32
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.
Try turning off the autocorrect.

Below are the steps to turn off autocorrect:
  1. Click on the File tab.
  2. Click on Options.
  3. In the Options dialog box, select Proofing.
  4. Click on the ‘AutoCorrect Options’ button.
  5. In the Autocorrect dialog box, within the Autocorrect tab, uncheck the ‘Replace text as you type’ option.
Uncheck replace text as you type


Source: Excel AutoCorrect: A Complete Guide + Time Saving Examples
 

georgec32

New Member
Joined
Mar 19, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi Dante,

Wow .. that actually worked! I don't understand why, but now it works fine. THANK YOU! I was going nuts trying to sort that one out :)

georgec32
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Im glad to help you, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,446
Messages
5,547,967
Members
410,820
Latest member
Prepost
Top