Open,new and save as an excel target file

[dacodac]

Hello,
this is my problem:

I would need to open a file with a simple macro from access.
I would like to be able to open a files from a default path.
And then choose one, to open it. Then I would like before to use it, save it to modificate its name and location (because its my template).
This where I am, but I can't open it actually :/ =


Sub SelectFile()


Dim fd As Office.FileDialog

' Create an object FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)

'Default path
fd.InitialFileName = "C:\Template_Path\"
' Title of the dialogue box
fd.Title = "Open a file ..."

' Not allow multiple selection
' there only one
fd.AllowMultiSelect = False

' Define type of file allow to be open
fd.Filters.Clear
fd.Filters.Add "Excel Workbook", "*.xlsx"

' Display a msgbox
If fd.Show() Then
MsgBox "Vous avez sélectionné le fichier : " _
& vbCrLf & fd.SelectedItems(1), vbInformation
End If

Set fd = Nothing

End Sub


Many thanks everyone
Regards.
D.

[June7]

If I understand, you want to:

1. find and select an Excel file
The file dialog coding should accomplish that

2. copy and rename the Excel file to another folder location
CopyFile method coding should accomplish that
CreateObject("Scripting.FileSystemObject").CopyFil e "from path\filename", "to path\filename", True

3. open the new Excel file
To open the Excel file as an object that Access can manipulate:
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("path\filename")
'code to do something with the file
oExcel.Quit
Set oBook = Nothing
Set oExcel = Nothing

or if you just want to open the file in Excel and then manually go to the Excel app and work with the file, use hyperlinking:
http://www.tutcity.com/access/allen-...ink.54866.html
http://allenbrowne.com/func-GoHyperlink.html

[dacodac]

Thanks for your answer!
I'm going to look at it!

Unfortunately the code doesn't open the selected file, even if it should be! Do you know why?

And yes, I just want to open a file from a specific location, once open and before to use it, save it in another location and different name then we could use the file.

I add this part of code, but it still doesn't open my selected file :/ :

Sub SelectRep()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Default path
.InitialFileName = "Template path\"
'Not allow user to make multiple selections
.AllowMultiSelect = False

'Set the title of the dialog box.
.Title = "Please select one file"
'Clear the current filters, and add our own.
.Filters.Clear
.Filters.Add "Excel Workbook", "*.xlsx"
If .Show = True Then 'If we want to choose more than one
varFile = .SelectedItems
'.FileList.AddItem varFile
'Next
'Call SaveNewLocation(varFile)

Else

MsgBox "You clicked Cancel in the file dialog box."

End If

End With

End Sub
Function SaveNewLocation(Name As String)
Dim fDialog As Object
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
Dim varFile1 As Variant
With fDialog
.AllowMultiSelect = False
.Title = "Select file location to export it ... "
'Target path to save the file
.InitialFileName = "Target path\"
If .Show = True Then
For Each varFile In .SelectedItems
SaveNewLocation = varFile1
Next
End If

End With

End FunctionMany thanks
Regards

[June7]

Have you step debugged? Follow the code as it executes. See where it deviates from expected behavior. Fix. Repeat. Refer to link at bottom of my post for guidance on debugging techniques.

Have you set VBA reference to: Microsoft Office 12.0 Object Library ?

Need to use drive letter or UNC in file path: .InitialFileName = "C:\Template path\"

This works for me:

Sub SelectCopyOpenExcelFile()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strFrom As String, strTo As String
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        'Default path
        .InitialFileName = "C:\Temp\"
        'Not allow user to make multiple selections
        .AllowMultiSelect = False
        'Set the title of the dialog box.
        .Title = "Select one file"
        'Clear the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "Excel Workbook", "*.xlsx"
        If .Show = True Then
            For Each varFile In .SelectedItems
                strFrom = varFile
            Next
        End If
    End With
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        'Default path
        .InitialFileName = "C:\"
        'Set the title of the dialog box.
        .Title = "Select destination folder"
        .Show
        strTo = .SelectedItems(1) & Mid(strFrom, InStrRev(strFrom, "\") + IIf(Len(.SelectedItems(1)) = 3, 1, 0))
    End With
    CreateObject("Scripting.FileSystemObject").CopyFile strFrom, strTo, True
    Application.FollowHyperlink strTo
End Sub

[dacodac]

Hi,

Yes I did it before with the step debugged, but everything was good, just that nothing appear.
It make me crazy!!!

I did check the reference :/

That "C:\Template path\", was just to indicate to everybody what it was, I have my own path.

Otherwise, your code, works!!!!
And I have a question about it, I don't understant this part : + IIf(Len(.SelectedItems(1)) = 3, 1, 0)

of the code --> strTo = .SelectedItems(1) & Mid(strFrom, InStrRev(strFrom, "\") + IIf(Len(.SelectedItems(1)) = 3, 1, 0))

Len will returns the lenght of the string (.SelectedItems) and IIf it's just a condition, but what is the impact of 1 or 0 then?

Last question, how I have to proceed if I want to change the name of the file when I indicate the target path?

Many thanks

[June7]

In debugging my code, I found that if I selected a drive root (like "C:\") to be the destination, the dialog returns "C:\" as the path but if I select a folder then the return is "C:\foldername" - without the ending "\". The Len function is to check if the path is just the root in which case the ending "\" is already there and code has to +1 to not capture the "\" in the strFrom path\filename. Step debug, look at the values of the variables in each case. A better method to check for presence of ending "\": Right(.SelectedItems(1),1)="\"

The msoFileDialogSaveAs constant is not available to Access VBA: http://support.microsoft.com/default...b;en-us;282335

If you want to allow user to change the filename, need to get that input from the user either by an InputBox() function or by reference to a textbox on form. Then concatenate that input into the strTo value.

Sub SelectCopyOpenFile()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strFrom As String, strTo As String, strInput As String
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        'Default path
        .InitialFileName = "C:\Temp\"
        'Not allow user to make multiple selections
        .AllowMultiSelect = False
        'Set the title of the dialog box.
        .Title = "Select one file"
        'Clear the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "Excel Workbook", "*.xlsx"
        If .Show = True Then
            For Each varFile In .SelectedItems
                strFrom = varFile
                Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
                'Default path
                .InitialFileName = "C:\"
                'Set the title of the dialog box.
                .Title = "Select destination folder"
                If .Show = True Then
                    strInput = InputBox("Enter filename") 'or reference to form textbox
                    If strInput <> "" Then
                        strTo = .SelectedItems(1) & IIf(Right(.SelectedItems(1), 1) = "\", "", "\") & strInput & ".xlsx"
                        CreateObject("Scripting.FileSystemObject").CopyFile strFrom, strTo, True
                        Application.FollowHyperlink strTo
                    End If
                End If
            Next
        End If
    End With
End Sub

[dacodac]

Hi,
I got an issue, I really don't know why, I tried to launch my code and I got this message:

The folder '...' isn't accessible. The folder may be located in an unavailable location, protected with a password, or the file name contains a / or \

I went to this : http://support.microsoft.com/kb/897853

But still doesn't work, any idea?

Thx

[June7]

Sorry, don't know what causes that error. The code works for me.