Save as methods with other file types

ado spm

New Member
Joined
Jul 24, 2007
Messages
2
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
FileCopy will copy any file. Something like :

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

ado spm

New Member
Joined
Jul 24, 2007
Messages
2
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.

Code:
<<<<<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

Code:
<<<<<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!
 

Forum statistics

Threads
1,181,100
Messages
5,928,058
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top