Run-time error '3051'

[DCritelli]

Hello,

I'm trying to write some vba that will import all excel spreadsheets in a folder into an access database. I had this working yesterday and now today I'm getting this 3051 error, saying the file is either open or has insufficient access permission.

The permissions on the folder and files are all set to full access and I've unchecked read only. I have tried this on different folders and tried creating a brand new excel file to try an import. Same error. I've included a sample so you can see what's going on. I'm using a browse folder api that I found. Then just calling that from a module and looping through all the .xls files in a folder.

Thanks

-d

 

[DCritelli]

Ok, I got this mostly working. The only problem I have left is during importing. Some of the workbooks raise a dialog box asking if I want to save the workbook.

I guess because the dialog is raised through excel, the Access macro keeps executing and ignores the dialog. This causes Access to hang after the code is done executing until I manually kill the excel process from task manager.

So, the question is. How would I go about catching the dialog box or automatically responding to it.

Here is the code I'm using. Basically it opens each excel file in the folder and loops through the sheets importing each one. There's probably a more efficient way to go about this but it's the only way I could get it to work.

Option Compare Database
Option Explicit

Public Sub ImportFilesInDirectory()
Dim strPath As String
Dim myFile As String
Dim objExcel As Object
Dim objWorkbook As Object
Dim colWorksheets As Object
Dim objWorksheet As Object
Dim strWorksheetName As String
Dim objRange As Object



strPath = "C:\Users\user\Desktop\Importer\Access Rates"
myFile = Dir(strPath & "\*.xls")

Do While myFile <> ""
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.workbooks.Open(strPath & "\" & myFile)
    objExcel.Visible = False
    Set colWorksheets = objWorkbook.Worksheets
    
        For Each objWorksheet In colWorksheets
            Set objRange = objWorksheet.UsedRange
            strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, objWorksheet.Name, _
                strPath & "\" & myFile, True, strWorksheetName
        Next
        
    myFile = Dir
    objExcel.Quit
    Set objExcel = Nothing
Loop

End Sub

 

[chergh]

Try objExcel.displayalerts = false

 

[LPurvis]

You don't seem to be saving the workbook ammendments.
i.e. just
objWorkbook.Save

Without suppression you'll naturally get prompted - and rightly so.

 

[DCritelli]

Yeah, I realize what I was doing wrong thanks to chergh. I'm new to programming and the sub procedure above is kind of a Frankenstein of snippets that I've mashed together for my own purposes. Fortunately though, because of this valuable learning experience I don't need a haircut anymore.