Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    transferspreadsheet function


    I'm trying to set up an transferspreadsheet function that transfers mulitple excel files into a access table called parts. the problem that I'm having is that all the files are excel but have different file names, how do i indicate in my tranferspreadsheet statement to call any file that is .csv

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    The following function will return an array with all of the files is a given folder having a particular extension:


    Code:
    Option Explicit
    
    Function FilesArray(PathStr As String, Ext As String)
            
        ' Function by Patrick Matthews
        
        ' Function looks in specified directory, and returns a zero-based array of full name and
        ' path for all files in that directory matching the specified extension (.???).
        ' Does *not* look in subfolders
        
        ' May be used in a worksheet or in code.  Code examples:
        
        ' To get the number of files, use this line in a master sub or function:
        ' MyArr = FilesArray("C:\MyFolder", ".doc")
        ' NumFiles = UBound(MyArr) + 1
        
        ' To get the paths:
        ' MyArr = FilesArray("C:\MyFolder", ".doc")
        ' Debug.Print MyArr(3) 'prints fourth file's path in immediate window--remember, zero-based!
        
        ' To get the file names:
        ' MyArr = FilesArray("C:\MyFolder", ".doc")
        ' Debug.Print Mid(MyArr(3), InStrRev(MyArr(3), "\") + 1)
        
        Dim Dict As Object 'Scripting.Dictionary
        Dim fso As Object 'Scripting.FileSystemObject
        Dim Fld As Object 'Scripting.Folder
        Dim Fil As Object 'Scripting.File
        Dim arr As Variant
        
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set Fld = fso.GetFolder(PathStr)
        
        Set Dict = CreateObject("Scripting.Dictionary")
        
        For Each Fil In Fld.Files
            If UCase(Right(Fil.Name, Len(Ext))) = UCase(Ext) Then Dict.Add Fil.path, Fil.path
        Next
        
        arr = Dict.Keys
        
        Set Dict = Nothing
        Set Fil = Nothing
        Set Fld = Nothing
        Set fso = Nothing
        
        FilesArray = arr
        
    End Function

    So now, use that function to populate an array with your files, and loop through the array invoking TransferSpreadsheet.

  3. #3
    Join Date
    Mar 2006
    Posts
    2

    transferred spread

    DoCmd.TransferSpreadsheet acImport, 8, _
    "import data", "C:\" * ".xls", False, "A4:c21"

    This is the problem I'm having. How do I specify any file on the C Drive

    This is giving me an error saying *.xls cannot be found

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    OK, assuming you implement the function I recommended last time:



    Code:
    Dim arr As Variant
    Dim x As Variant
    
    arr = FilesArray("c:\", ".xls")
    For Each x In arr
        DoCmd.TransferSpreadsheet acImport, 8, _
            "importdata", x, False, "A4:C21"
    Next

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Is there a function to do this....
    By Nowherefast in forum Access
    Replies: 2
    Last Post: 12-31-2008, 08:08 AM
  2. Can't get TransferSpreadsheet to work
    By Valeda in forum Import/Export Data
    Replies: 0
    Last Post: 07-19-2007, 08:04 AM
  3. SELECT FUNCTION help please
    By scott munkirs in forum Queries
    Replies: 0
    Last Post: 10-17-2006, 07:44 AM
  4. transferSpreadsheet
    By rabbit in forum Import/Export Data
    Replies: 1
    Last Post: 06-09-2006, 04:01 PM
  5. Need Help for Dlookup function
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 01-04-2006, 08:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums