I guess I'm confused. I thought what you WANTED was to have them mess with a copy, not the original.
Dim strFileName As String
Dim xlApp As Object
Dim xlWkBook As Object
Dim xlWkSheet As Object
Dim strID As String
Dim qry As DAO.QueryDef
Dim rec As DAO.Recordset
Dim fField As DAO.Field
Dim j As Long
strID = Nz(Me!pr_ID, -1)
If strID = -1 Then Exit Sub
strFileName = Environ("USERPROFILE") & "\" & Format(Now, "yymmddhhnnss") & " - Planning.xlsm"
'MsgBox strFileName & vbCrLf & ExistFile(Environ("USERPROFILE") & "\", , , False)
If bSavePJ(strFileName, "1") Then
Set xlApp = Nothing
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing = False Then
xlApp.Visible = True
Set xlWkBook = xlApp.workbooks.Open(strFileName)
Hi. I could be off, but I thought that's what I suggested earlier. The user clicks a button, you save a copy of the attachment on their computer and then open it. That keeps the original attachment untouched. No?
Sorry I did not understand "programatically"...I thought you meant I had to take the file and copy the file on their laptops. So if I have an attachment in table 'T_Reporting' as first and only record called 'Report.pbix', how can I get the user an dialogbox to save it where he wants?
Alright...then it's like I already said ... Save a copy to disk, and open the copy for them...
Private Sub BI_Attachment_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset2
Dim rsA As DAO.Recordset2
Dim fld As DAO.Field2
Dim strFullPath As String
'Dim fpath As String
'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("T_Reporting")
Set fld = rst("BI_Attachment")
'Get the recordset for the Attachments field
Set rsA = fld.Value
'Navigate through the table
Do While Not rst.EOF
'Let user chose path
fpath = Application.FileDialog(msoFileDialogFolderPicker)
strFullPath = fpath & "\Reporting PMO.pbix"
'Save file
rsA("Reporting PMO.pbix").SaveToFile strFullPath
Loop
'Open the file
End SubHi. You can use the FileDialog object for that. However, I wonder why you would care where the user wants to store the file, or even let them. If you were to allow users to double click on the attachment to open it normally, users won't need to select a location where to save it (hint, Access saves it in a default location). So, you can also give them the same experience. When they click on your button, simply save the file "somewhere," they don't need to know where it is. In fact, you may have to delete it anyway afterwards to avoid any potential copy conflicts.
Private Sub BI_Attachment_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset2
Dim rsA As DAO.Recordset2
Dim fld As DAO.Field2
Dim strFullPath As String
Dim fpath As String
Dim bolOk As Boolean
fpath = Application.FileDialog(msoFileDialogFolderPicker)
bolOk = Len(fpath) > 0
Do Until bolOk
If MsgBox("You did not select any folder. Try again?", vbQuestion + vbYesNo) = vbYes Then
fpath = Application.FileDialog(msoFileDialogFolderPicker)
bolOk = Len(fpath) > 0
Else
Exit Do
End If
Loop
If bolOk Then
'Dim fpath As String
'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("T_Reporting")
Set fld = rst("BI_Attachment")
'Get the recordset for the Attachments field
Set rsA = fld.Value
strFullPath = fpath & "\Reporting PMO.pbix"
'Save file
rsA("FileData").SaveToFile strFullPath
'housekeeping
Set rsA = Nothing
rst.Close
Set rst = Nothing
Set dbs = Nothing
'open file
Application.FollowHyperlink strFullpath
End If
End SubWell, I for one thought the idea was to store a "master" in the table and force users to save any changes to it wherever they wanted if they chose to do so. This means no file dialog code is really needed when you have the Save As feature in the native application. I figured the link I provided would be OK for that but I guess not - and only 19 lines of code.
hey;
Hi. That's a nice approach. But if the Attachment Control is locked, how did you save the attachment in the first place?
Hi. Glad to hear you got it sorted out. Good luck with your project.
hello;
