Dir() Function empty for certain drives

[johnctholen]

Hi all,

I have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.


The issue:

• The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name)
• The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)

I have no idea what is causing this. Does anyone see something here?
Let me know if I need to give more information. Thanks for reading.

I should also mention I am pretty new to VBA and the Access world.

Option Compare Database
 
Private Sub btn_ImportData_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String
Dim strFilePath As String
On Error Resume Next
 
'Uncomment out the following section if you want to delete
'current data from the tables:
'DoCmd.DeleteObject acTable, "Certificate Details"
'DoCmd.DeleteObject acTable, "Customer_Device"
'DoCmd.DeleteObject acTable, "Eccentricity Test 1"
'DoCmd.DeleteObject acTable, "Eccentricity Test 2"
'DoCmd.DeleteObject acTable, "Eccentricity Test 3"
'DoCmd.DeleteObject acTable, "EccentricityAnalysis"
'DoCmd.DeleteObject acTable, "EOI"
'DoCmd.DeleteObject acTable, "Recommendations"
'DoCmd.DeleteObject acTable, "Repeatability"
'DoCmd.DeleteObject acTable, "Scale Condition"
'DoCmd.DeleteObject acTable, "Screens"
'DoCmd.DeleteObject acTable, "SearchParameters"
'DoCmd.DeleteObject acTable, "Selected Weights"
'DoCmd.DeleteObject acTable, "Selected Weights Detail"
'DoCmd.DeleteObject acTable, "Strain Load"
 
' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set objExcel = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
'Assign user FilePath to variable
'strFilePath = txt_FilePath
'Dim strFolderName As String
'strFolderName = BrowseFolder("What Folder you want to select?")
' Replace C:\MyFolder\ with the actual path to the folder that holds the EXCEL files
strPath = BrowseFolder("What Folder you want to select?") & "\"
 
If strPath <> "\" Then MsgBox ("Importing Data from folder: " & strPath) Else: MsgBox ("Please Select a Folder to Import")
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = vbNullString
blnReadOnly = True ' open EXCEL file in read-only mode
[COLOR=red]strFile = Dir(strPath & "*.xls")[/COLOR]
MsgBox (strPath)
MsgBox (strFile)
intWorkbookCounter = 0
Do While strFile <> ""
      intWorkbookCounter = intWorkbookCounter + 1
      Set colWorksheets = New Collection
      Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
            blnReadOnly, , strPassword)
      For lngCount = 1 To objWorkbook.Worksheets.Count
            colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
      Next lngCount
      ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
      objWorkbook.Close False
      Set objWorkbook = Nothing
      ' Import the data from each worksheet into a separate table
      For lngCount = colWorksheets.Count To 1 Step -1
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                  colWorksheets(lngCount), _
                  strPath & strFile, blnHasFieldNames, _
                  colWorksheets(lngCount) & "$"
      Next lngCount
      ' Delete the collection
      Set colWorksheets = Nothing
      ' Uncomment out the next code step if you want to delete the
      ' EXCEL file after it's been imported
      ' Kill strPath & strFile
      strFile = Dir()
Loop
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing
 
End Sub

 

[gemma-the-husky]

Double check that the folderpath has a closing backslash. That might be the problem.

 

[johnctholen]

That doesn't seem to be the problem. The path has a backslash.

 

[gemma-the-husky]

OK try again. You placed the folder on the desktop? perhaps you are browsing to the wrong user's desktop folder.

Try doing it in windows first to verify the folder definitely contains the file. The other thing, you are filtering .xls files. Is the file an .xlsx?

 

[JHB]

I'll suggest you to comment out all the error handling you've in the code, at least until it runs perfect.

 

[spikepl]

How exactly do you select desktop? What do you type?

 

[gemma-the-husky]

desktop is in

c:\users\%username%\desktop

that's why I thought maybe the OP was navigating to the wrong desktop folder.

 

[johnctholen]

I changed the code to search for *.xlsx files and it was successfully able to grab the desktop file which it would not before. Although I guess this solves my problem I am still quite confused.

When I only had *.xls it was still able to grab the file from my network drive, even though that was a .xlsx file just like the desktop one (they are the same exact file and filename within the same folder name). That is why I ruled this out as the source of the error.