I have 50 of the same excel workbooks. These workbooks have named range I need to import into my Access Database. I have the code figured out to import all workbooks from a specific directory, but I need to import only the named range of each workbook. I don't know where in the code I can put that command.
The named Range is "Import_2013_1st_Qtr"
Here is the code I have now:
Private Sub Import_2013_1ST_Click()
' This subprocedure loops determines whether
' any Excel files exist in the folder stored
' in the strcPath constant; if there are,
' this subprocedure imports the data in the
' Excel files.
' Store the directory Path:
Const strcPath As String = _
"C:\Users\eric.berger\Documents\$ Fin Sp\1 Inactive Projects\Data\3-31-2013 Division Workbooks"
' Store the name of the table into which
' the data will be imported
Const strcTableName As String = "$-2013 1st Qtr Inactives"
Dim strPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String
' See if path constant ends in a backslash:
If Right(strcPath, 1) = "\" Then
strPath = strcPath
Else
strPath = strcPath & "\"
End If
' Loop through the Excel files in the folder
' (if any) and build file list:
strFile = Dir(strPath & "*.xlsx")
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
' See if any files were found:
If intFile = 0 Then
MsgBox strcPath & vbNewLine & vbNewLine _
& "The above directory contains no Excel " _
& "files.", _
vbExclamation + vbOKOnly, "Program Finished"
GoTo Exit_Import_From_Excel
End If
' Cycle through the list of files and import into
' Access, creating a new table if necessary:
For intFile = 1 To UBound(strFileList)
strFullPath = strPath & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, strcTableName, _
strFullPath, True
Next
MsgBox UBound(strFileList) & " file(s) were imported", _
vbOKOnly + vbInformation, "Program Finished"
Exit_Import_From_Excel:
Exit Sub
Any assistance would be great.... Thankyou
Bookmarks