Script works but skips files in defined range.

[stevedeer]

Hello.

I need help with this bit of VBA. The script executes successfully but it skips some files about 259 out of 350 are getting copied. All links have been validated and work.

Any feedback will be much appreciated.

Please Login or Register  to view this content.

[Bernie Deitrick]

Do you have any files with the same names, but in different folders? Check for existence before copying, and if they exist, then create a unique name

Please Login or Register  to view this content.

[AlphaFrog]

I'm not sure wht the problem is. Bernie's suggestion sounds plausible. Also consider duplicate hyperlinks within the Range? I would suspect the issue is with the Hyperlinks, the Range, or the files, and not so much with the VBA code itself.

On another note, you can loop through the hyperlinks within your visible range using the hyperlinks collection...

Please Login or Register  to view this content.

[stevedeer]

Ok it brings in the right number of files now however it is duplicating pdfs for some reason. I have verified that each hyperlink is unique and the file names in the source locations are unique from each other.

Example below is a list that i tested this on. Originally my script would only bring in the first pdf in the list. Now with Bernies updated script it looks at all the instances but duplicates the first PDF and

Sources its looking at.

..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HL.pdf
..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HL-I.pdf
..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HM.pdf
..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HM-I.pdf
..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HS.pdf
..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HS-I.pdf


What it pastes in folder. It uses same pdf and adds the row number to beginning. Its like its not reading the Characters in the hyperlinks past the HL.

01 - Controller - Delta - DOW-340-HL.pdf
36-01 - Controller - Delta - DOW-340-HL.pdf
37-01 - Controller - Delta - DOW-340-HL.pdf
38-01 - Controller - Delta - DOW-340-HL.pdf
39-01 - Controller - Delta - DOW-340-HL.pdf
40-01 - Controller - Delta - DOW-340-HL.pdf

[Bernie Deitrick]

I am guessing that you copied your hyperlinks and changed the display string but not the actual file address. Look in cells L36, 37, 38, 39, and 40.

Otherwise, there are errors and the .Address value is somehow not being properly updated from those cells.

What does this show?

Please Login or Register  to view this content.

[stevedeer]

So when I run your error check it brings back this.

..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HL.pdf
..\..\..\..\Cutsheets\Delta\Software\01 - Controller - Delta - DOW-340-HL.pdf

but when i click on the link it takes me to the right file and then also when i go into edit hyper link it gives me this as the address for each


..\..\..\..\Cutsheets\Delta\Software\01%20-%20Controller%20-%20Delta%20-%20DOW-340-HL-I.pdf
..\..\..\..\Cutsheets\Delta\Software\01%20-%20Controller%20-%20Delta%20-%20DOW-340-HM.pdf

[Bernie Deitrick]

How about?

Please Login or Register  to view this content.

[stevedeer]

Gives back
2
2

[Bernie Deitrick]

I think that having more than 1 hyperlink in a cell is the source of your error - you are always assuming Hyperlink(1) is correct (and I honestly don't know how to add two hyperlinks to one cell....)

Please Login or Register  to view this content.

Maybe it can all be fixed if you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[stevedeer]

This worked out. And yes running the new FindError script did reveal multiple associated hyperlinks.

How is that possible? I can not find where the extra links exist. I assume it may be from the original document creator copying and pasting.

What can I do in the future to avoid this?

[Bernie Deitrick]

Try running this to clean out the extra HLs: I have assumed that later HLs are the desired HLs.

Please Login or Register  to view this content.

[stevedeer]

Great thanks

So know I have modified the code to create a Dir and then put the files in that Dir.

It worked initially multiple times and tests runs with no issues but once it was saved down, closed, and reoppened it errors out on file path not found for the Mkdir command.

To help I am running this off a Networked drive.

Please Login or Register  to view this content.

[stevedeer]

Great thanks

So now I have modified the code to create a Dir and then put the files in that Dir.

It worked initially multiple times and tests runs with no issues but once it was saved down, closed, and reoppened it errors out on file path not found for the Mkdir command.

To help I am running this off a Networked drive.

Please Login or Register  to view this content.

[Bernie Deitrick]

Is the error because the folder exists?

Please Login or Register  to view this content.

Or that the network folder is not the default drive? Then, the ..\ will not work - not sure what your network drive is named....

Please Login or Register  to view this content.

[stevedeer]

The folder does not exist. I did try the If statement method prior to posting and got same error.

What has been discovered is when ?CurDir is run in Immediate window my local drive pops up... Is there a way to get excel to recognize the path from which it was launched?

Also the current network drive letter is Y:

C:\Users\steve\Documents

[stevedeer]

Figured it out

Please Login or Register  to view this content.

[Bernie Deitrick]

Please Login or Register  to view this content.

[stevedeer]

Ok so everything is working greqat now except when I save and close out now the hyperlin address path errors out. However if I click the hyperlinks and navigate to the document manually and then run script again it all updates successfully and runs.

Any idea what could be causing that?

[Bernie Deitrick]

I think that it is the ..\ part of the file path. You either need to set an absolute path "Y:\folder...." or change to the correct directory tree when the file is opened. You could use

Private Sub Workbook_Open()
ChDrive "Y:\"
End Sub

In the codemodule of the ThisWorkbook object.

[stevedeer]

Ok so below is the original code and now it calls a new Sub that takes pdfs from one location based on a visible cell range and then put them in the directory and then calls another module to merge the pdfs. In the second module there is a variable strPath that when the full folder path is defined it works fine. However trying to use a structure like "..\Submittal Packaged\BOM PDF\" it gets stuck in the while loop. I have debugged and watched it step through and find every pdf file in the folder but instead of not seeing the end it loops back to the beginning.

The below code is configured in the way I am having issues.

Please Login or Register  to view this content.