Open/Save Excel Sheet - Force it to save to a specific directory

[PhilOwens]

Hi all,

As with all database developments I keep getting more and more requirements piled on me and was hoping that someone could help me out with this one as it is outside my knowledge!!!

One of my forms shows project information and has the capability of browsing and attaching files to the current record (works fine). There is a command button that opens up a template excel file that we use in the office, this is were I get lost!

The excel form opens up fine but I have been asked if I can automatically force the excel file to save to the correct place (I have a filing system that attached documents are saved to) once the user has finished with it.

I have absolutely no idea if this is possible as I have never tried anything like this before!

Any help, even if it is just a confirmation that this is beyond the capability of access, is most appreciated!!

Cheers

Phil

[FishVal]

Hi all,

As with all database developments I keep getting more and more requirements piled on me and was hoping that someone could help me out with this one as it is outside my knowledge!!!

One of my forms shows project information and has the capability of browsing and attaching files to the current record (works fine). There is a command button that opens up a template excel file that we use in the office, this is were I get lost!

The excel form opens up fine but I have been asked if I can automatically force the excel file to save to the correct place (I have a filing system that attached documents are saved to) once the user has finished with it.

I have absolutely no idea if this is possible as I have never tried anything like this before!

Any help, even if it is just a confirmation that this is beyond the capability of access, is most appreciated!!

Cheers

Phil

It is definitely possible and quite easy to do. Plz provide me the code of the button used to open Excel template.

[PhilOwens]

It is definitely possible and quite easy to do. Plz provide me the code of the button used to open Excel template.

Cheers....I was hoping that it was possible but wasnt holding my breath!

Code below opens the template file.

Private Sub Template_Click()

template_filepath = "\\UK\Main\Projects\templates\Blank_Comments.x ls"
FollowHyperlink (template_filepath)

End Sub

[FishVal]

Cheers....I was hoping that it was possible but wasnt holding my breath!

Code below opens the template file.

Private Sub Template_Click()

template_filepath = "\\UK\Main\Projects\templates\Blank_Comments.x ls"
FollowHyperlink (template_filepath)

End Sub

Ok.

I'm not sure whether you can do it opening XL file via FollowHyperlink.

Here is another solution. It works fine.

 
Option Compare Database
Const FolderToSaveTo = "C:\Temp\"
Const TemplatePath = "C:\template.xls"
 
Private Sub Template_Click()
 
    Dim appExcel As Excel.Application
    Dim wkbWorkBook As Excel.Workbook
 
    On Error Resume Next
 
    Set appExcel = CreateObject("Excel.Application")
 
    With appExcel
        .UserControl = True
        Set wkbWorkBook = .Workbooks.Open(TemplatePath)
        If Err <> 0 Then
            MsgBox "Template file not found", vbOKOnly + vbCritical, "Error"
            .Quit
            GoTo ExitSub
        End If
        .Visible = True
    End With
 
    wkbWorkBook.SaveAs FolderToSaveTo & UniqueName & ".xls"
 
ExitSub:
    Set wkbWorkBook = Nothing
    Set appExcel = Nothing
 
End Sub
 
Private Function UniqueName() As String
    UniqueName = "CreatedOn - " & Format(Now, "mmm-dd-yyyy hh-mm-ss")
End Function
 
 

Define your paths in rows 2,3

Good luck.

[PhilOwens]

What can I say other than thanks!!

Works a treat.

Cheers FishVal, you saved me from one hell of a headache!