Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Run-time error 3011 - Access db could not find the object

    Hello All!

    I'm using the DoCmd to import the latest excel sheet from a specific folder (G:\BOC\SCO\BBVOD_FBP\FBP_DB\ActionSheet) to my table (_01_As_new). Please see code below:

    Code:
    DoCmd.TransferSpreadsheet acImport, 10, "_01_AS_new", GetLatestFile("xlsx", strPath), True, "Action Sheet"
    The excel workbook has 2 worksheets, I am trying to pull "Action Sheet".

    When running the code I get the following error:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	8 
Size:	12.3 KB 
ID:	16392

    How do I indicate the automation to look for the file in [G:\BOC\SCO\BBVOD_FBP\FBP_DB\ActionSheet] as opposed to [C:\Users\512394\Documents]?

    Any help is greatly appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what is defining the current path
    is it the value in strpath
    is it the function getlatestfile
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    53
    Hi Healdem,

    Here is the code used where the current path is defined. Am having trouble modifying to point to a specific directory. Any thoughts?

    Code:
    Function GetLatestFile(strFileType, strPath As String) As String
    
    Dim oFSO As Object, oFolder As Object, oFile As Object
    Dim sPath As String
    Dim latestDate As Date
    Dim myFile As String
        
        latestDate = DateSerial(1900, 1, 1)
        
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        If oFSO.FolderExists(strPath) Then
            Set oFolder = oFSO.GetFolder(strPath)
            For Each oFile In oFolder.Files
                If LCase$(Right$(oFile.Name, Len(strFileType) + 1)) = "." & strFileType Then
                    If oFile.DateCreated > latestDate Then
                        latestDate = oFile.DateCreated
                        myFile = oFile.Name
                    End If
                End If
            Next oFile
            Set oFolder = Nothing
        Else
            MsgBox "Cannot find " & strPath
        End If
        
        Set oFSO = Nothing
        
        GetLatestFile = myFile
        
    End Function

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what does this code do
    what do you not understand
    what attempts have you made to take 'ownership' of the code by going through line by line and understanding what its doing. its very, very dangerous to grab code from elsewhere and stuff it into your project without understanding what it is doing. even more dangerous if that code is coming from an outside source such as a forum
    if you dont' know what the code does, then put a breakpoint on the function and step through the code. if you don't know how to do that, then put aside half a day and learn about debugging in Access. that half a day may seem an expensive waste of time, but it will probably repay itself in the first 2..3 weeks of embracing debugging

    depends where your source file is coming from. if tis alwasy from the same directory then prepend that directory name to the file name

    eg
    Code:
    dim strFileLocation as string
    dim strFileName as string
    strFileLocation = "G:\BOC\SCO\BBVOD_FBP\FBP_DB\ActionSheet"
    strFileName = "6.8 Action Sheet"
    DoCmd.TransferSpreadsheet acImport, 10, "_01_AS_new", strFileLocation & strFileName, True, "Action Sheet"
    it also depends on how you are identifying the file to import. it coudl come throghj a fiel dialog, or a file scripting object(FSO)
    as a first 'stab' at it I'd want to check that the file actually exisits before rewroking the code. ferinstance is the file name '6. 8 Action sheet', or is it '6.8 Action sheet'
    when debugign code satrt off by identifying where the problem lies. one 'good' way of doingthis is to remove the variability. hardcode values at first*, prove the method is right, and then gradually evolve the method so you reach the final design objective. start small, prove which bits of the code work, then move on, prove the next bits.

    not knowing where the fault lies is usually the main problem

    * so instead of using
    prove its not a missing file y repalcing the GetLatestFile("xlsx", strPath) in
    Code:
    DoCmd.TransferSpreadsheet acImport, 10, "_01_AS_new", GetLatestFile("xlsx", strPath), True, "Action Sheet"
    with, say
    Code:
    DoCmd.TransferSpreadsheet acImport, 10, "_01_AS_new", G:\BOC\SCO\BBVOD_FBP\FBP_DB\ActionSheet\6. 8 Action Sheet.xlsx", True, "Action Sheet"
    ...orwhatever the file is actually called with a fully qualified path to that location.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •