VBA to Mass Import from Excel

[Kimbertha]

Hello All:

How can I modify the following code to:

1) Specify a tab and range
2) Give the imported file the same name as the source (minus the .XLS extension)

The parts in red show where I have figured out how to successfully modify.

The VBA code below works great, except it imports the first tab of a multi-tab workbook. The first tab on my 1,200+ bookbooks contains instructions, not the data that I want.

Thanks for any suggestions!

Kim

======

Private Sub Command0_Click()

' This subprocedure 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 and then moves the files to
' the folder stored in the strcNewPath constant.


' Store paths:
Const strcPath As String = _
"C:\Users\rxp\Desktop\Excel\"
Const strcNewPath As String = _
"C:\Users\rxp\Desktop\Excel1\"

' Store the name of the table into which
' the data will be imported
Const strcTableName As String = "Table1"

Dim strPath As String
Dim strNewPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String
Dim strFullNewPath As String


' See if path constant ends in a backslash:
If Right(strcPath, 1) = "\" Then
strPath = strcPath
Else
strPath = strcPath & "\"
End If

' See if new path constant ends in a backslash:
If Right(strcNewPath, 1) = "\" Then
strNewPath = strcNewPath
Else
strNewPath = strcNewPath & "\"
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


' Loop through the list of files:
For intFile = 1 To UBound(strFileList)

' Initialise paths:
strFullPath = strPath & strFileList(intFile)
strFullNewPath = strNewPath & strFileList(intFile)

' Import into Access:
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, strcTableName, _
strFullPath, True

' Copy file to new location:
FileCopy strFullPath, strFullNewPath

' Delete old file:
Kill strFullPath

Next

MsgBox UBound(strFileList) & " file(s) were imported", _
vbOKOnly + vbInformation, "Program Finished"

Exit_Import_From_Excel:


Exit Sub

End Sub

[help_me_with_access]

Hello All:

How can I modify the following code to:

1) Specify a tab and range
2) Give the imported file the same name as the source (minus the .XLS extension)

The parts in red show where I have figured out how to successfully modify.

The VBA code below works great, except it imports the first tab of a multi-tab workbook. The first tab on my 1,200+ bookbooks contains instructions, not the data that I want.

Thanks for any suggestions!

Kim

to specify a tab in excel, you can use either of the following code lines:

workbook.worksheets("sheet name").range("start cell", "end cell")

or:

workbook.worksheets(sheet index integer).range("start cell", "end cell")

sheet indexes in excel are base-1. in other words, there is no sheet #0. the sheets are indexed, in order, based on their created date, NOT their left-to-right appearance in the workbook.

to give a FILE or a TABLE the same name as the imported data? if you're talking table, use access VBA's docmd() method. simple as that. and I might be mistaken, but isn't there an arg in the transferspreadsheet() method where you can tell the routine what range or even SHEET to import? if there is, put the sheet name in there first. try that. I haven't done this work in a while, but it's none-the-less still not that difficult, or shouldn't be...