Filedialog msofiledialogfilepicker Results

Hi,

Firstly - very novice at programming using VB - I tend to search the net for code, and try to amend it to what I want to do - sometimes successfully.

I have an existing Access Database that has a series of buttons. I am trying to code one of the buttons, so that on click it will open (not import) an existing file - by allowing the user to select the file, and in this case, excel (but not necessarily limited to excel). I have found the following code, but it's not working, giving me a compile error at the very first dim statement. Can anybody point out to me where I am going wrong, or suggest an alternative method to what I am trying to do? Thanks in advance.

Private Sub FeesPaid_Click()


'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
MsgBox "The path is: " & vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub

I need to import data from an Excel 2007 spreadsheet into a temp table in Access 2007. I cannot always count on the Excel file being in the same location every time.


The following DoCmd.TransferSpreadsheet works perfect every time. But notice that the path to the file is hard coded.


DoCmd.TransferSpreadsheet acImport, 10,"Tbl_VendorPrices_TempImport", "C:menuprobidsimport35", True, "A1:I1000"

I have tried many different ways to navigate to the file. This one below works very well. However, when you pick the file it instantiates an instance of Excel. So when I try to import the file it fails saying that the file is already open by another user.

What I need is a way to navigate to the file location, and only return to VB the PATH, and store it as a string variable. Then I can have the TransferSpreadsheet use the variable to locate the file.


Dim Dlg As FileDialog
Dim txtFilePath As String
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.title = "Select the file you want to import"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With


Any Thoughts?
Phred

I am using the code below to open the file picker and grab an object. I can then translate the object into Base64 and write it out into an XML file without importing and saving the object. However, in certain cases I already have the path stored in the database and would like to use that path instead of opening the file picker.

is there a way to substitute the path "c:filesmyPDF.pdf" for "fd.SelectedItems(1)" and grab that object without opening the file picker? My goal is to avoid importing the object into a table, but just grab the object and translate it to Base64 then write it out into an XML file.
_______________________________________

Private Sub cmbAddBinaryData_Click()


FilePickerControl = False
'Open the File Picker control
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
If fd.Show = -1 Then
txtfiletoupload = fd.SelectedItems(1)
End If


Open txtfiletoupload For Binary Access Read As #1

ReDim ByteImage(1 To LOF(1))
Get #1, , ByteImage

Close #1

strData = EncodeBase64(ByteImage)

Me.txtAttBinaryDataPath = txtfiletoupload

FilePickerControl = True

Access 2007.
FileDialog for importing is fine - use msoFileDialogFilePicker to pick the full path of the file to import data from.

But when exporting and trying to use FileDialog with msoFileDialogSaveAs it throws an error.

Help says "The msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access."
So how do you do it? Sure this is a common question and there's a work-around somewhere...

Thanks.

I built an Access 2010 database about two years ago for a client. My client wanted to scan customer contracts and save them with the database. I knew there were two methods; save the scanned documents in an Attachment file or save just the path to the file as a Hyperlink. At the time, (maybe I didn’t do enough research), I decided to use the Attachment file method. I probably also underestimated the quantity of scanned documents the customer would have and the impact on the database file size. From the start, the database was split into a Front End and a Back End. At inception, the Back End was about 3MB in size. Today, it is over 660MB in size and growing with every scanned document.

I started doing research online to figure out how I could export all of the scanned documents from the Attachment field, save them to a folder, and then add their location to a new table and field so the customer could still access them. It’s important to note that each scanned contract is associated with a customer order and I would need to ensure that whenever my customer went to a customer’s order, they would see only those scanned contracts. I never found in one place a “How To Export Attachments” article so I decided to summarize here what I did in the hope that it helps others that need to reverse stored documents/pictures/etc. from an Attachment field and save them in a folder. Maintaining the relationship to the location in the database are now being saved was critical.

Before you begin, make sure you back up your Back End data as well as your Front End. Also, please hold the feedback on how I named my fields and/or tables. I’m comfortable with it and it works for me. Yes, I know sometimes I need to use brackets on my field/table names.

One sample code that I used was from the Microsoft Access 2010 Programmer’s Reference manual. I modified the code slightly and it is shown below:

Code: Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim rsA As DAO.Recordset2 Dim rsB As String Dim fld As DAO.Field2 Dim OrdID As DAO.Field2 Dim strFullPath As String 'Get the database, recordset, and attachment field Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Order Table") Set fld = rst("Scan") Set OrdID = rst("OrderID") 'Navigate through the table Do While Not rst.EOF 'Get the recordset for the Attachments field Set rsA = fld.Value rsB = OrdID.Value 'Save all attachments in the field Do While Not rsA.EOF If rsA("FileName") Like strPattern Then 'To Export the data, use the line below strFullPath = strPath & "" & rsA("FileName") 'Make sure the file does not exist and save If Dir(strFullPath) = "" Then rsA("FileData").SaveToFile strFullPath End If 'Increment the number of files saved SaveAttachmentsTest = SaveAttachmentsTest + 1 End If 'Next attachment rsA.MoveNext Loop rsA.Close 'Next record rst.MoveNext Loop rst.Close dbs.Close Set fld = Nothing Set rsA = Nothing Set rst = Nothing Set dbs = Nothing End Function Some key points.
The table that contains the attachments field is [Order Table]. The field for each order is [OrderNbr]. The Attachment field’s name is [Scan]. My Attachment field when viewed in the Query By Entry, (QBE), shows:


Scan.FileDataScan.FileNameScan.FileType

I created a temporary Form with two Command Buttons on it:


Export Attachments
This had a simple macro on the On Click event that was:
RunCode and the Function Name was: SaveAttachmentsTest(“R:Attachments”) where “R:Attachments” was the location of the folder where I was going to save the scanned contracts to. When the Function was executed, it exported each scanned document in the order they were input into the database to that folder. (Although I haven’t shown this, I modified the above code slightly to modify as the file name and show the [OrderNbr] just to be able to verify the order and the associations between the scanned contract file name and the [OrderID] were in the right order.)
Run Append Query
I created a new table that I called Attachments Table. It has three fields in it:
[AttachNbr] which is an AutoNumber field[OrderID] which is a Number field that will correspond/establish the relationship back to the [OrderID] field in the [Order Table][FileN] which is a Hyperlink field to store the location where the file is saved
The Append query used the following SQL:
INSERT INTO [Attachments Table] ( OrderID, FileN )
SELECT [Order Table].OrderID, [Scan].[FileName] & "#" & "R:Attachments" & [Scan].[FileName] & "#" AS FileN
FROM [Order Table] WHERE ((([Order Table].Scan.FileName) Is Not Null));

This Query appends to the [Attachments Table] the [OrderID] from the [Order Table], (to the [OrderID] field), and a string, “R:Attachments”, (see NOTE below), which is the folder location where I just exported the scanned contracts to, and concatenated to it is the file name from the [Scan].[FileName] part of the Attachments/[Scan] field. NOTE: In order to get the Hyperlink data to be correctly stored in the field, you must use the “#” symbols as show in the SQL string. A Hyperlink field contains three parts separated by pound signs “#”. The template is: Display Text # file name including the path # Any reference within the file, (i.e. a sheet name if you’re importing an Excel spreadsheet). I found a helpful reference to this at: http://allenbrowne.com/casu-09.html

I also found that in my VBA editor that I needed to go to Tools, References and check Microsoft Office 14.0 Object Library.

I then modified my Order Form to remove the Attachment field input/delete button and then added a new Command button to open up my new Attachment Form, (Default View: Continuous Forms). This form has a couple of events:


On Open it goes to a new recordOn Before Insert it triggers a macro to SetValue of the [OrderID] field to equal the [OrderID] field of the Order Form where this form was opened from. This links the hyperlink location and file with the OrderID.The form is based on a Query that uses as its only source the Attachments Table. The three fields from the Attachment Table are used in the query and the [OrderID] field has criteria that equals Forms![Customer Order Form]![OrderID] to make sure that only records for the customer in the form are shown. The form also has two Command buttons:
Attach: It uses this code:
Private Sub cmdPopulateHyperlink_Click()
'First, set a Reference to the Microsoft Office XX.X Object Library

Code: Dim strButtonCaption As String, strDialogTitle As String Dim strHyperlinkFile As String, strSelectedFile As String 'Define your own Captions if necessary strButtonCaption = "Save Hyperlink" strDialogTitle = "Select File to Create Hyperlink to" With Application.FileDialog(msoFileDialogFilePicker) With .Filters .Clear .Add "All Files", "*.*" 'Allow ALL File types 'Test line so I can debug/compile the code End With 'The Show Method returns True if 1 or more files are selected .AllowMultiSelect = False 'Critical Line .FilterIndex = 1 'Database files .ButtonName = strButtonCaption .InitialFileName = vbNullString .InitialView = msoFileDialogViewDetails 'Detailed View .Title = strDialogTitle If .Show Then For Each varItem In .SelectedItems 'There will only be 1 'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address) strSelectedFile = varItem strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile Me![FileLocX] = strHyperlinkFile Next varItem End If End With End Sub

Delete: Simple Delete macro
Note: the above VBA code I found on a few different sites

I tested the functionality and once I was satisfied everything works, I deleted my [Scan] field from the [Order Table], compacted the database and went from 665MB to 4.6MB.

I hope this is helpful to all who need to reverse an internally stored attachment to saving it externally.

Chuck

Hey, I am using Access 2010 on a cloud network.

I am having trouble with my hyperlink field to a pdf document. The user sets the hyperlink themselves by clicking btnGetLink and searching for the file. When the user selects the file, the hyperlink is put in the txtLink text box which is bound to my table. The hyperlink in the text box works fine when copied into windows explorer (when I go to edit hyperlink and select the hyperlink, not the actual text in the text box) but clicking on the hyperlink itself yields an error message "An unexpected error has occurred". Here is a sample hyperlink address: "K:CommonUserDavidSigned LeasesLease528.pdf" The K: drive is the cloud network drive which I am also running access off of.

This is the code:

Code: Private Sub btnGetLink_Click() Dim strButtonCaption As String, strDialogTitle As String Dim strHyperlinkFile As String, strSelectedFile As String 'Define your own Captions if necessary strButtonCaption = "Save Hyperlink" strDialogTitle = "Select File to Create Hyperlink to" With Application.FileDialog(msoFileDialogFilePicker) With .Filters .Clear .Add "All Files", "*.*" 'Allow ALL File types End With 'The Show Method returns True if 1 or more files are selected .AllowMultiSelect = False 'Critical Line .FilterIndex = 1 'Database files .ButtonName = strButtonCaption .InitialFileName = "K:CommonUserDavidSigned Leases" .InitialView = msoFileDialogViewDetails 'Detailed View .Title = strDialogTitle If .Show Then For Each varItem In .SelectedItems 'There will only be 1 'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address) strSelectedFile = varItem strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile Me![txtLink] = strHyperlinkFile Next varItem End If End With End Sub Public Function fGetBaseFileName(strFilePath As String) As String 'This Function accepts the Absolute Path to a File and returns the Base File 'Name (File Name without the Extension) 'Make absolutely sure that it is a valid Path/Filename If Dir$(strFilePath) = "" Then Exit Function Dim strFileName As String Dim strBaseFileName As String strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "")) strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1) fGetBaseFileName = strBaseFileName End Function If anyone could provide some assistance as to why clicking the hyperlink yields an error message, that would be great!

Thanks

**I did not write this code. It is from http://bytes.com/topic/access/insigh...hyperlink-form

I need to import PDF and XLS objects and convert them to binary. The following code doesn't work, apparently because the "fd.SelectedItems(1)" is the path of the object instead of the object itself.

If I dim "fileToUpload" as an object, I get a "run time '91' Object variable or With block variable not set".

If I don't specifically Dim "fileToUpload", when I get to the last line below I get "run time '424' Object required".

Anyone know the magic words?


Dim fd As FileDialog
Dim ImageToBytes() As Byte
Dim ImageCode As String

FilePickerControl = False
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
If fd.Show = -1 Then
FileToUpload = fd.SelectedItems(1)
End If

ImageToBytes = System.IO.File.ReadAllBytes(FileToUpload)

Hello all,

Import Issue:

I am trying to figure out how to import data from a specific worksheet in all excel files in a single folder into separate tables via transferspreadsheet (VB). The problem I am facing is that the below will not work. Can anyone help me edit this?

Option Compare Database
Private Sub Command0_Click()
Dim blnHasFieldNames As Boolean
Dim strWorksheet As String, strTable As String
Dim strPath As String, strPathFile As String

blnHasFieldNames = True
strPath = "C:Users"
strWorksheet = "Data"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, _
blnHasFieldNames, strWorksheet & "$"

strFile = Dir()
Loop
End Sub


Export Issue:

I have some code below that will not work. I am trying to display a dialogbox to select a table within a database and then export that table to an excel spreadsheet with the same table name (excluding the .mdb extension). Can anyone help edit this code?Private Sub Form_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

fDialog.Filters.Add "All Files", "*.*"

If fDialog.Show = True Then
For Each varFile In fDialog.SelectedItems
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyNewSpreadsheet", varFile, True
Next
End If
End Sub

I would like to print out 8 pictures as part of my report. Due to size issues I would assume using a link would be the ideal way to go?

I am trying to let the user select the pictures so under the table that stores the records I made 8 picture fields to store the links. Under the form I tried using
With Application.FileDialog(msoFileDialogFilePicker)
like my Inside Out book does but my computer doesnt recognize this, is there another way I can let the user select the file path for the pictures that they want to show? Any tips or suggestions would be appreciated, thanks.

My skill level is moderate.

My access DB opens and imports an Excel spreadsheet successfully. When I test it again it says Excel is in use.

I checked the Task Manager, no Excel. I shut down my Access DB and restarted. No success.

Finally I shut everything down and rebooted. No success. Still says Excel is open.

I have read most of the posts and tried several code suggestions. None work.

I have attached a screen snap of error messages. My variable TxtFilePath works fine.

This import code has worked 2 times.

Private Sub CmdImport_Click()
Dim Dlg As FileDialog
Dim txtFilePath As String

Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.title = "Select the file you want to import"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else

Exit Sub
End If
End With

DoCmd.TransferSpreadsheet acImport, 10, "Tbl_ImportVendorsTemp", txtFilePath, True, "A1:I407"
End Sub

I have tried many different chunks of close Excel code. I understand Excel may be running invisible. I read about that but it was above my level to understand or implement. After rebooting I am not sure that is actually my problem.

I need to test and see if Excel is open. Then I need to close or Kill Excel. Then import the new spreadsheet. It is fine if Excel opens and displays the spreadsheet.

No variation of the code below works.

Dim objXL As Object
Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("C:TempBook1.xlsx")
xlWB.Close False
objXL.Quit

Thanks, Phred Attached Thumbnails   Reply With Quote 01-13-2012, 09:42 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,126 Check this thread which discusses this issue with the TransferSpreadsheet method
http://forums.aspfree.com/microsoft-...el-413629.html

Hey,
I am getting a compile error at the start, it says it is not defined. Here is my code:

Code: Private Sub AddressInfo_Click() Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) fd.InitialFileName = "c:abc*.xls*" fd.Show FileName = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1) Dim oExcel As Object, oWb As Object Set oExcel = CreateObject("Excel.Application") Set oWb = oExcel.Workbooks.Open(FileName:=FileName, Password:="xyz") DoCmd.TransferSpreadsheet acImport, 8, "Address_Information", FileName, True End Sub any ideas? thanks in advance!

My database has upgraded from Access 2002 to Access 2003. Previously I used msoFileDialogFilePicker to open the Word document associated with the Record on the form. Unfortunately in the upgrade the Microsoft file containing this VB property has got lost. To save purchasing a new file I have rewritten the code that doesn't work. But I am stuck and cannot see a way to call up the Word record from the Access form. How did you go about this before Microsoft introduced the FilePicker Filedialog?

i have 2 database front ends (the 'old' one that i am trying to wean the office from; and the new one...)

both have a report that is essentially the exact same document, however, the resoution of the picture, that is accociated with the current record, is HORRIBLE in the new one... (the old guy is not a problem at all...) (this is not going over well !)

the only difference that i can note might be the way that the images' hyperlink is added to the related table

-----------------------------------
on the good one, the link is added on a pop-up form, with this code:

With Application.FileDialog(3) ' 3 is a constant: msoFileDialogFilePicker
.Title = "Select page"
.Filters.Add "All Files", "*.*"
.Filters.Add "JPGs", "*.JPG"
.FilterIndex = 4
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
result = .Show
If (result 0) Then 'result = 0 if nothing was selected
fileName = Trim(.SelectedItems.Item(1))
'filename contains the path you want.
End If
End With
Me.CatalogSheet = "#" + fileName

------------------------------------
on the bad one, the link is added on a sub form with tis code:

vPrintOrder = ...
With Application.FileDialog(3) ' 3 is a constant: msoFileDialogFilePicker
.Title = "Select page"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.Filters.Add "JPGs", "*.JPG"
.Filters.Add "BMP's", "*.BMP"
.FilterIndex = 2
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
result = .Show
If (result 0) Then 'result = 0 if nothing was selected
CatalogSheetLink = Trim(.SelectedItems.item(1))
Dim sSQL As String
sSQL = "INSERT INTO tbeAdditionalPages (type, printCatalogSheet, BaseCatalogSheet, CatalogSheetLink, PrintOrder, IsMountingDetail) " _
& " Values(" _
& "'" & Forms!frmSpec.Type & " ', " _
& "true, " _
& "false, " _
& "'#" & CatalogSheetLink & " ', " _
& vPrintOrder & ", " _
& "false" _
& ");"
CurrentDb().Execute sSQL, dbFailOnError
Forms![frmSpec].chldFixtureCuts.Requery
Else
Response = MsgBox("...nothing selected to add", vbInformation + vbOKOnly, "Add Catalog Sheet Error")
End If
End With

----------------
any thoughts... would be REALLY appreciated,
Mark

I'm inserting a hyperlink into the same field, in the same table, in two (2) different manners (I do need both: 1, adds a new record, and the other edits an existing record.) The first of them works, the other does not (and I'm looking for why it doesn't...

in the one that does work, (the field that contains the hyperlink is being edited)
the user selects a file to which the hyperlink is connected, and the existing record is updated

in the one that does NOT work, (an set of records is being added to the table)
I am inserting a set of records from another table. In that data source table, those records already have the hyperlink, and the hyperlinks do work. When they are inserted into the new table, they do not link ("file path not found")

any thoughts whatsoever would be greatly appreciated,
mark


------------------------

***this method does work

With Application.FileDialog(3) ' 3 is a constant: msoFileDialogFilePicker
.Title = "Select page"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.Filters.Add "JPGs", "*.JPG"
.Filters.Add "BMPs", "*.BMP"
.FilterIndex = 2
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
result = .Show
If (result 0) Then 'result = 0 if nothing was selected
fileName = Trim(.SelectedItems.Item(1)) 'filename contains the path you want.
End If
End With
If Len(fileName) > 0 Then
Me.CatalogSheetLink = "#" + fileName
End If


***this method does not work

Dim sSQL As String
sSQL = "INSERT INTO tbeAdditionalPages (type, printCatalogSheet, BaseCatalogSheet, CatalogSheetLink, PrintOrder, IsMountingDetail) " & _
"SELECT '" & Forms![frmSpec].[Type] & "', true, true, '#' & CatalogSheetLink, printOrder, false " & _
"FROM FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & Forms![frmSpec].Manufacturer.Value & _
"' and CatalogNumber = '" & Forms![frmSpec].CatalogNo.Value & "';"
CurrentDb().Execute sSQL, dbFailOnError


Not finding an answer? Try a Google search.