Drop Multiple Files on VBS to Pass to Excel

[Rekd]

I've got a VBScript file that I'm trying to get to populate an Excel spreadsheet with the file names dropped onto it. It works with a few files, but when I try to dump like 21 or more files on it I get an error saying it can't process that many arguments.

Dim arg, cnt
Set objExcel = CreateObject("Excel.Application")
If WScript.Arguments.Count = 0 Then
   WScript.Echo  "Drop files on this script to load into Excel spreadsheet."
Else
objExcel.Visible = True
objExcel.Workbooks.Add
   For each arg in WScript.Arguments 
cnt = cnt + 1
objExcel.Cells(cnt, 1).Value = arg
   Next
End If
WScript.Quit(0)

Is there a way around this?

 

[Rekd]

Anyone?

 

[Joe4]

I must admit that my VBS skills are a bit lacking, though my Excel VBA skills are pretty good. If we can come up with an Excel VBA solution, would that be acceptable?

Can you explain a little bit more of exactly this whole process should work, so we get a bigger picture of what you are trying to accomplish?

Where/how are these file names "dropped" to this script?

Do you just want the file names added to an Excel file as a list?

 

[Rekd]

I must admit that my VBS skills are a bit lacking, though my Excel VBA skills are pretty good. If we can come up with an Excel VBA solution, would that be acceptable?

Can you explain a little bit more of exactly this whole process should work, so we get a bigger picture of what you are trying to accomplish?

Where/how are these file names "dropped" to this script?

Do you just want the file names added to an Excel file as a list?

Thanks, Joe.

I want to be able to drop a group of files on the script (using mouse), usually PDF files of mechanical drawings from Solildworks (aka blue-prints), and have the file names (with or without folders) put into Excel.

From there I do things like create a link to each drawing, estimate each part's cost, send them out to machine shops for quotes, and highlight/track the hi and low bids among other things.

What I've been doing in the past is opening a command prompt, going to the folder that the files are listed in (always a differnt folder), doing a "dir *.pdf /b > files.txt", then opening the txt file in Excel. This works ok but is kind of slow. I could do a batch file instead, but some times I am not using all the .PDF's in that folder, that's why I decided to try to do it with a script using drag/drop.

Using the drag and drop method works very well on a low number of files, but some of the assemblies I'm doing have 50 or more files, and the script chokes when it gets around 20 or 21 files. (Haven't figured out what the gating factor is because sometimes it chokes on 21 files, sometimes 19, perhaps the length of the strings combined.)

If there's another way to accomplish this I'd like to consider it. I may end up begging and pleading with the IT department to let me make my own .exe file to do this. It's been a while but if I remember correctly VB6 does not have this limitation.

 

[Joe4]

See if this might be a start of something you can use.

Here is an Excel macro that when you run it, will open the File Browser where you can select all your desired PDF files. Once you have selected them all, it will list them down column A of the Excel file and create a hyperlink to each.

Sub WriteFilesToList()

'   Parts of script coutesy of Zack Barresse [URL]http://www.mrexcel.com/forum/showpost.php?p=1017983&postcount=5[/URL]
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim varFiles()
    Dim strName As String
    Dim strPath As String
    Dim i As Long
    
'   Open file browser to select files
    varFiles = Application.GetOpenFilename(filefilter:="PDF Files *.pdf, (*.pdf)", MultiSelect:=True)
    
    If TypeName(varFiles) = "Boolean" Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   Loop through all selections
    For i = 1 To UBound(varFiles)
''      capture names of file and path separately, if needed for anything else
''        strName = Right(varFiles(i), Len(varFiles(i)) - InStrRev(varFiles(i), Application.PathSeparator))
''        strPath = Left(varFiles(i), Len(varFiles(i)) - Len(strName))
'       List files
        Cells(i, 1) = varFiles(i)
'       Add hyperlinks
        Cells(i, 1).Hyperlinks.Add anchor:=Cells(i, 1), Address:=varFiles(i)
    Next i
    
    Application.ScreenUpdating = True
    
    Set wb = Nothing
    Set ws = Nothing
 
End Sub

Does this sound like it is on the right track?

I imagine that instead of creating the list to the Excel file that contains the macros, you may want to write it out to a new Excel file. If so, that shouldn't be too hard to add.

 

[Rekd]

See if this might be a start of something you can use.

Here is an Excel macro that when you run it, will open the File Browser where you can select all your desired PDF files. Once you have selected them all, it will list them down column A of the Excel file and create a hyperlink to each.

Sub WriteFilesToList()
 
'   Parts of script coutesy of Zack Barresse [URL]http://www.mrexcel.com/forum/showpost.php?p=1017983&postcount=5[/URL]
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim varFiles()
    Dim strName As String
    Dim strPath As String
    Dim i As Long
 
'   Open file browser to select files
    varFiles = Application.GetOpenFilename(filefilter:="PDF Files *.pdf, (*.pdf)", MultiSelect:=True)
 
    If TypeName(varFiles) = "Boolean" Then Exit Sub
 
    Application.ScreenUpdating = False
 
'   Loop through all selections
    For i = 1 To UBound(varFiles)
''      capture names of file and path separately, if needed for anything else
''        strName = Right(varFiles(i), Len(varFiles(i)) - InStrRev(varFiles(i), Application.PathSeparator))
''        strPath = Left(varFiles(i), Len(varFiles(i)) - Len(strName))
'       List files
        Cells(i, 1) = varFiles(i)
'       Add hyperlinks
        Cells(i, 1).Hyperlinks.Add anchor:=Cells(i, 1), Address:=varFiles(i)
    Next i
 
    Application.ScreenUpdating = True
 
    Set wb = Nothing
    Set ws = Nothing
 
End Sub

Does this sound like it is on the right track?

I imagine that instead of creating the list to the Excel file that contains the macros, you may want to write it out to a new Excel file. If so, that shouldn't be too hard to add.

I'm doing something very similar to that to create links to the actual solidworks files in the same spreadsheet but I don't run that until after I've got the file list of pdf's into the spreadsheet.

The reason I'm not doing that for the PDFs is because I'm usually already in the folder that has the PDFs, and it would be easier to just grab the files while I'm there and drop them on the script, before Excel gets opened.

You did trigger a thought for a solution I might be able to use earlier in the process... Once the drafter finishes the preliminary drawing I get a notice to start processing the estimates. I take all the drawing files from solidworks and run a VBA macro from there that automatically creates the PDFs from the drawings.

There's no reason I can't just create a text (.csv) file in the same directory I'm writing the PDFs to and write each file to it as I'm creating the PDFs. That wouldn't help for new files that usually show up a few days later, but for the initial task it might work.

 

[Joe4]

I'm doing something very similar to that to create links to the actual solidworks files in the same spreadsheet but I don't run that until after I've got the file list of pdf's into the spreadsheet.

This does both (create list and hyperlinks) in the same step. If you are going into Excel anyway at some point, it shouldn't add too much to the process.

The reason I'm not doing that for the PDFs is because I'm usually already in the folder that has the PDFs, and it would be easier to just grab the files while I'm there and drop them on the script, before Excel gets opened.

If the files are always in the same location, you can add code to always have the file browser open in that folder. Save a few steps anyway, if using the Excel solution.

'   Open file browser to select files
    ChDrive "C:"
    ChDir "C:\MyPDFFiles\"
    varFiles = Application.GetOpenFilename(filefilter:="PDF Files *.pdf, (*.pdf)", MultiSelect:=True)

That's all I really got. As I said, I have not done much with VBS. Hopefully, I gave you a few "nuggets" to chew on anyway.

Hope you get it all worked out.

 

[Rekd]

If the files are always in the same location, you can add code to always have the file browser open in that folder. Save a few steps anyway, if using the Excel solution.

The files are always in a different location. Each engineer has a folder and they have up to 5 or 6 sub folders each where the work on their part of the assembly.

I think I'm going to pursue the .csv file being created as I'm creating the PDFs in Solidworks. That would probably be the most efficient.

Thanks again for your help, it's appreciated.