Save as methods with other file types

[ado spm]

Hi,

I'm using Excel 2003 and Windows XP. Currently, I have a few sheets with hyperlinks in them that open files that are not XL files types, namely SolidWorks, ProE, PDFs, etc. However, because these files are very large and are going to be stored on a CD or DVD, I'd really like to have a "Save as..." dialog box come up when these file links or buttons are hit. I just need something that will allow the user to copy files from the CD to the computer in an intuitive way.

The only information I can find shows how to save or copy just XL files:
http://www.rondebruin.nl/saveas.htm
http://www.exceltip.com/st/Use_the_built-in_dialogs_using_VBA_in_Microsoft_Excel/416.html
...and numerous other websites

If anybody could help me find either a way of doing this or an alternative, it would be much appreciated. FYI, there are 891 files linked and I'm also using VBA and buttons to sort the large table.

Thanks,
ADO

 

[BrianB]

FileCopy will copy any file. Something like :

FileCopy "h:\myfile.pdf", "c:\myfile.pdf"

 

[ado spm]

Thanks Brian! That worked out great. I've been working all day and here's a small piece of what I've ended up with. Of course, nobody can see the contents of the spreadsheet, but maybe this will help somebody else who has a similar question.

<<<<<Workbook (Code)>>>>>

Private Sub Workbook_Open()
dirA = True 'Declares sort direction
dirB = True
dirC = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count = 1 And ActiveCell.Value <> "" Then
        Dim InterSectRange As Range
        Set InterSectRange = Application.Intersect(Target, Range("D:N"), Range("2:65536"))
        If Not InterSectRange Is Nothing Then   'If in appropriate range
               saveHelper Target.Value, Target.Offset(0, 11).Value, Target.Offset(0, 22).Value
        End If
    End If
End Sub

<<<<<Module (Code)>>>>>

Public dirA As Boolean
Public dirB As Boolean
Public dirC As Boolean

Sub SortA()
    sortHelper dirA, "A2"
    dirA = Not dirA 'Change direction
End Sub

Sub SortB()
    sortHelper dirB, "B2"
    dirB = Not dirB
End Sub

Sub SortC()
    sortHelper dirC, "C2"
    dirC = Not dirC
End Sub

Sub sortHelper(Direction As Boolean, Column As String)
    Range("A:AJ").Select    'Select range to sort
    If Direction Then   'Sort ascending
        Selection.sort Key1:=Range(Column), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    Else    'Sort descending
        Selection.sort Key1:=Range(Column), Order1:=xlDescending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End If
    Range(Column).Select    'Reset selection
End Sub

Sub saveHelper(ByVal Ending As String, ByVal Name As String, ByVal Path As String)
    Dim FileFilter As String
    FileFilter = Switch(Ending = "SLDFTP", "SolidWorks Form Tool (*.sldftp), *.sldftp", _
                              Ending = "PRT", "ProEngineer Part File   (*.prt.1), *.prt.1", _
                        Ending = "DXF", "DXF Drawing     (*.dxf), *.dxf", _
                          Etc........)
    fileSaveName = Application.GetSaveAsFilename(Name, FileFilter, 1, "Save As...")
    If fileSaveName <> False Then
        FileCopy Path & Name, fileSaveName
    End If
End Sub

Thanks again!