The code works fine below. I need help inserting a tablename argument where it brings in the filename I chose instead of "*" or me coding a
particular filename. I have several files I need to bring in and want them all to keep their name when imported to Access tables.
I am working on this today and really appreciate the help.



Function ImportFile() As Integer


Dim fs As Object
Dim FileSpec As String
Dim strFile As String
Dim tablename As String
Dim i As Integer
'Dim getfilename As String

FileSpec = OpenTextFile("c:\download\")



 DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel3, "*", FileSpec, True

ImportFile = 1

End Function

Not sure what you're doing but I'll guess that you are trying to use the file system object which requires a reference to the scripting runtime. It doesn't seem like you are using many of the variables you've dimensioned. Are the files you are seeking to link always excel files? Are they always in the same folder? If so, do you want to replace the links if they already exist? Do you want to run a batch of files at a time?

I would avoid the file system object because many deployment targets run on systems that refuse to enable the scripting runtime because of the ease with which it can be exploited to do damage. VBA provides means to get file names from folders (and folders) with the Dir function and you can use this to navigate the file system and populate a listbox with a list of files in a folder from which you may select one or more files. If you say with a bit more detail what you are attempting to accomplish it will be easier to lend assistance.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

I am new with VBA, I found code to browse directory (that worked fine) but when I clicked the filename the code I had would create the table in Access with the filename.

I was going to try to take this one step at a time.

Let me see if I can explain. Please forgive me if I am not totally clear.

Example: C:\Partsfolder\CurrentPartsfolder\multiplefiles\wi thmuliplespreadsheets

There are three levels of folders before getting to the spreadsheets.(as I am seeing it)

The user opens the one "multiplefiles" folder and selects a spreadsheet and imports it into the database. Then I need the code to look in all the other folders again "multiplefiles" folder and find the spreadsheet that has the highest (max)number value in the name shown as "_1_1"(example: ABC0000555_1_1, or ABC0000555_1_2 in this case I need the file named ABC0000555_1_2 because is higher than _1_1.)

There could be hundreds of what I called "multiplefiles folders under the currentPartsfolder that will need to be searched. And after the spreadsheets are imported or linked I need all of the data into one table along with the ABC0000555 part of the file name in one column and the "1_2" part of the filename in another column of the tablename.

Your questions:

Are the files you are seeking to link always excel files? Yes

Are they always in the same folder? Not same folder (multiple folders under one or two main folders).


If so, do you want to replace the links if they already exist? The links will be deleted everytime the program is run to show new data.


Do you want to run a batch of files at a time? I might consider this to be like a batch, but not really familiar with batch.

I will be working on this today. Please let me know if you need more information. I really can use the help. It has been a learning experience. Thanks

I've written a very short demo that uses an api procedure to pop up a folder selection dialog form. Selecting a folder will populate a multiselect listbox with the names of all the files matching the file specification. By default, it finds *.xls files in your selected folder. The Dir function is used to populate an array which is in turn used to feed a callback function used to fill the listbox. The user may select one or more file names displayed in the list. Clicking a link button iterates the itemsSelected collection of the list giving you the filename (stripped of the extension) as a table name and the full path for the link specification. No need to use any file system object. The listbox displays file name, file size and modified date and you can click the header to toggle sort direction on any column. Presumably the file you want to link will be recent but it is defaulted to sort by name.

In Access 2000, the file is 148k unzipped. If you would like a copy, please provide an email address to which I may forward the file. Any requests to my personal email address please. I will not respond to requests posted to this thread. I will discuss any aspects of the procedure on list but the overall demo is too broad for general discussion.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

I'll break this into short posts. First, to use the Win32 API to browse available folders. In the next post I'll explain the 'GetFiles' procedure commented out here.

Assume a form with a textbox named txtFilePath and a command button named cmdGetFolder having the following click event:

Private Sub cmdGetFolder_Click()
    Me.txtFilePath = BrowseFolder("Select a Folder for the file list to display.")
' GetFiles
End Sub

In a regular module (not code behind a form) use the following code to enable the browse for folder:

Option Compare Database
Option Explicit

Private Declare Function SHBrowseForFolderA Lib "shell32" (lpBrowseInfo _
  As BrowseInfo) As Long

Private Declare Function SHGetPathFromIDListA Lib "shell32" _
  (ByVal pidl As Long, ByVal pszPath As String) As Long

Private Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Private Const BIF_RETURNONLYFSDIRS = &H1

Public Function BrowseFolder(strDialogTitle As String) As String
    Dim bi As BrowseInfo
    Dim szPath As String

    With bi
        .hOwner = hWndAccessApp
        .lpszTitle = strDialogTitle
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
    szPath = Space$(512)
    If SHGetPathFromIDListA(ByVal SHBrowseForFolderA(bi), ByVal szPath) Then
        BrowseFolder = Left$(szPath, InStr(szPath, Chr(0)) - 1)
    Else
        BrowseFolder = ""
    End If
End Function

The result of the procedure is that you can return a folder from the file system and display the path in a textbox.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

The next step is to define the GetFiles procedure which uses a module level array variable to hold the file names, dates and size and a module level long variable to contain the list count. These variables are required to hold the data needed to fill the list box displaying all the file names in the user selected foldler. To dimension the module level variables in the code behind the form, paste the following two lines at the top of the module but after any Option statements:

    Private mlngI As Long
    Private marDocNames As Variant

Then paste in the GetFiles procedure. This procedure calls the Dir function which returns the names of files at the path specified. If you call it in a loop, it will continue to find the next file in a folder until you exit the loop when an empty string is returned. In this case, the procedure may reside in the code behind the form (as there are references to the textbox containing the file path from the previous function) and also gets the size and date modified and sets the values in the module level array variable. This procedure could also be placed in a stand alone module as a function that receives the file path from a calling procedure in a form and returns the array to the caller. In this case, it assumes you have a listbox control on your form named 'lstFile'.

Private Sub GetFiles()
    Dim strFileCrit As String
    Dim strFileName As String
    Dim strFilePath As String
    Dim lngI As Long

    If Len(Me.txtFilePath) Then
        strFileCrit = "*.xls"
        strFilePath = Me.txtFilePath & "\"
        strFileName = Dir(strFilePath & strFileCrit, vbDirectory)
        ReDim marDocNames(2, 0)
        Do While Len(strFileName)
            If Not (strFileName = ".." Or strFileName = ".") Then
                ReDim Preserve marDocNames(2, lngI)
                marDocNames(0, lngI) = strFileName
                marDocNames(1, lngI) = FileDateTime(strFilePath & strFileName)
                marDocNames(2, lngI) = FileLen(strFilePath & strFileName)
                strFileName = Dir
                lngI = lngI + 1
            Else
                strFileName = Dir
            End If
        Loop
        mlngI = lngI
        Me.lstFile = 0
    Else
        MsgBox "Please select a target path."
        Exit Sub
    End If
    Me.lstFile.Requery
End Sub

Note the line:

         strFileCrit = "*.xls"

You may instead use a textbox with a default value set in the property sheet as "*.xls" in order to permit users to substitute different file specifications. The "*.xls" tells the procedure to return only excel workbook files. If the specification was changed to "*.xlt", it would return only excel templates. "*.*" would return all file in the folder.

How to use the array to populate the list will be my next post.


Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

In order for an Access listbox to display the contents of an array, there is really only one choice and that is to use a call back function. It is possible to build a delimited list or add the file names into a table and use the list or table as a row source, but the delimited list is limited to 2K characters of data and the table method is slow and causes bloat. A call back function is a procedure you write that Access calls to fill the list and refill as the list is navigated. In order to use a callback function, you need to set a few properties. In this case, it is necessary to set the 'Row Source Type' property to the name of the callback function. Do not use an equals sign or trailing parantheses, just the name. In this example, the callback function is in the code behind the form and named 'FileSource' so the Row Source Type property should read simply:

FileSource

I have also set the 'Bound Column' to a special value. Curiously, this is not a zero based value because binding to column(0) is done by setting the bound column property to '1'. In this case, we can set the property to '0' and it will return the index of the array data from which it is filled.

Additional properties that need to be set are:

'Column Count' --- 3
'Column Width' --- 3"; 1.2"; 0.9"

The call back function placed in the code behind the form:

Private Function FileSource(fld As Control, ID As Variant, Row As Long, _
  Col As Long, Code As Variant) As Variant
    Dim ReturnVal As Variant

    ReturnVal = Null
    Select Case Code
        Case acLBInitialize
            ReturnVal = mlngI
        Case acLBOpen
            ReturnVal = Timer
        Case acLBGetRowCount
            ReturnVal = mlngI 'the count of items in the array
        Case acLBGetColumnCount
            ReturnVal = 3 'to match the colum count property
        Case acLBGetColumnWidth
            ReturnVal = -1
        Case acLBGetValue
            ReturnVal = marDocNames(Col, Row) 'the array column and row data values
        Case acLBGetFormat
        Case acLBEnd
    End Select
    FileSource = ReturnVal
End Function

Next post will be how to allow a user to select one or more files from the list and return the spreadheet name for the table name and the full file path to create the links.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

If you have set one of the multiselect properties for the list box, using a command button named cmdLink to activate the linking, using your link code, can be put into effect as shown below. There is an additional if test to verify the users have chosen an Excel spreadsheet file. The procedure will link as many excel files as your users select at one time. If a table name already exists, Access will simply append an incremental numeric suffix to the name and repeat the link.

Private Sub cmdLink_Click()
    Dim varItem As Variant
    Dim strTbl As String
    Dim strFile As String

    For Each varItem In Me.lstFile.ItemsSelected
        If Right$(marDocNames(0, varItem), 3) = "xls" Then
            strFile = Me.txtFilePath & "\" & marDocNames(0, varItem)
            strTbl = Left$(Me.lstFile.Column(0, varItem), Len(Me.lstFile.Column(0, varItem)) - 4)
            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel3, strTbl, strFile, True
        End If
    Next
End Sub

I'll leave my postings at that. I use labels as sort toggles for sort direction inset into a frame including the listbox so that it appears like a conventional file list and enables sort in either direction. I set the PictureData property of an image control as a direction indicator on the current sort toggle to indicate sort direction and change the label specialeffect property on mouse down and up to mimic the action of a Command Button with the advantage that clicking a label does not take the focus from the list during the sort. I use a multicolumn array quick sort routine to sort making it easier for users to select files they want from the list.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]