VBA: Import Specific Tab from Excel


I'm looking to implement more controls on the VBA importation of Excel. I'm looking to import a specific tab on a spreadsheet but the problem I will have is if someone renames the tab. Does anyone know of a better way?

I thought of the Excel codenames for the worksheets; however, I am unable to figure that part out on my own. Any advice? Thank you.
Sub ImportExcel()

Dim strWorkBookName As String, _
    strExcelTabName As String, _
    strTableName As String, _
    strWorkBookPath As String, _
    strWorkBook As String

strWorkBookPath = CurrentProject.Path

'strWorkBookName = "VBA Solution - RV2 ML-BAC Recon.xls"
strWorkBookName = "Book5.xls"

strWorkBook = strWorkBookPath & "\" & strWorkBookName

strExcelTabName = "RV2"
strTableName = "tblRv2Import"

DoCmd.TransferSpreadsheet _
    transfertype:=acImport, _
    spreadsheettype:=acSpreadsheetTypeExcel9, _
    tablename:=strTableName, _
    FileName:=strWorkBook, _

End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
if the sheet/tab name was changed, do you have a way of knowing what information you want to import.

if you do, you can open the excel file (in codes) read and verify, then import
Kelvin SparksCommented:
You need to invoke the range option
    transfertype:=acImport, _
    spreadsheettype:=acSpreadsheetTypeExcel9, _
    tablename:=strTableName, _
    FileName:=strWorkBook, _
    hasfieldnames:=True  _
   Range:= strExcelTabName & "!"
Not quite sure of the syntax here. But range can have the shettname and/or the range name
Shino_skayAuthor Commented:
Capricorn1, thanks for replying again. You're saying I can open the excel file in Access, verify if rows 1 has the correct fields before importing? That's exactly what I'm looking for, I can do it in Excel. Actually I already written the VBA codes in Excel but it's such a pain to compare two database downloads that has 30 columns each with anywhere from 80-250 rows then to do calculations on them etc. The maintenance of it is very painful when my boss wants a new type of calculation.
SQL seems the way to go.

Kelvin, thanks for that tip on the exclamation point, I was wondernig why my range wouldn't work. I'm just concern if the end users changes the tab name (for some reason), I'm going ot get emails saying the codes don't work. I guess I'll have to lock the tab name somehow.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Rey Obrero (Capricorn1)Commented:
<You're saying I can open the excel file in Access, verify if rows 1 has the correct fields before importing?>

Yes, do you need help doing this?
if you do, Upload a copy of the Excel file and give details what to look for...
Shino_skayAuthor Commented:
i need a lot of help. Unfortunately, I can't upload the original data as they are deemed "proprietary". I did include all the column names and a little randomly generated data.

Each week, I download from 2 different databases, one for the week's data (4-9 tab), one fo the week's price (4-9 pricing tab).

The 1st step I would do is a vlookup to grab all the prices from "4-9 Pricing" and fill them in the appropriate fields starting in columns "V" in the "4-9  tab" using the column name "AdminNum" as the lookup value.

Afterwards, I would write the column names (as found in the tab "Calculated Fields") to the end of the data in the "4-9" tab, in this case, after columns "AF". Those "Calculated Fields", I would write simple calculations such as "Days to Maturity" which is:

Public Function YearsToMaturity(DaysToMaturity As Long) As String
    Select Case DaysToMaturity
        Case Is > (365 * 20)
            YearsToMaturity = "20yr+"
        Case Is > (365 * 10)
            YearsToMaturity = "10yr-20yr"
        Case Is > (365 * 5)
            YearsToMaturity = "5yr-10yr"
        Case Is > (365 * 2)
            YearsToMaturity = "2yr-5yr"
        Case Is > 365
            YearsToMaturity = "1yr-2yr"
        Case Else
            YearsToMaturity = "0yr-1yr"
    End Select
End Function

I do the same steps for the previoius week's data and I compare them for differences. The difference list is another laundry list of simple calculations, averages, max/mins but I feel once I figure out how to import the data, it should be relatively easy.

The tricky part is, the download for "4-9" tab, the field names would change without much notice, in excel, I would write

shtBondPrice.Rows(1) .Find(strBondPriceColumns_PriceSht(i), LookIn:=xlValues, lookat:=xlWhole).Cells.Column

If I get an error, I would know someone changed, or maybe even totally excluded the fieild name

Thanks Capricorn, I hope I wasn't too confusing in my explanation.

Shino_skayAuthor Commented:
sorry capricorn, I should have been more clear, from all the columns on the tab "4-9", I only need to make sure a few of them exist:

Maturity Date
USEQ Outstanding Notional
Bond Price w/o Credit upload
Bond Price with Credit upload
CS01 (USD) upload
Duration upload

Rey Obrero (Capricorn1)Commented:
try this codes

Sub verifyColNames()
Dim xlObj As Object, xlSht As Object
Dim j, i, fName As String, colCount As Integer

fName = CurrentProject.Path & "\sample.xls"
    Set xlObj = CreateObject("Excel.Application")
        xlObj.Workbooks.Open fName
    With xlObj
        For j = 1 To .worksheets.Count
        Set xlSht = .activeworkbook.worksheets(j)
            For i = xlSht.usedrange.columns.Count To 1 Step -1
                If xlSht.cells(1, i).Value = "Maturity Date" _
                    Or xlSht.cells(1, i).Value = "AdminNum" _
                    Or xlSht.cells(1, i).Value = "USEQ Outstanding Notional" _
                    Or xlSht.cells(1, i).Value = "Bond Price w/o Credit upload" _
                    Or xlSht.cells(1, i).Value = "Bond Price with Credit upload" _
                    Or xlSht.cells(1, i).Value = "CS01 (USD) upload" _
                    Or xlSht.cells(1, i).Value = "Duration upload" Then
                    colCount = colCount + 1
                End If
            If colCount = 7 Then
                MsgBox "The name of the tab is " & .activeworkbook.worksheets(j).Name
                Exit For
            End If
    End With
    Set xlObj = Nothing

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shino_skayAuthor Commented:
ahhhh, i see, you're controlling excel thru Access vbe and checking if the 1st row contains the column names, So after this, I should use the docmd.transfer spreadsheet to a temporary table and then append the data to the table where my sql is looking at.

My final step would be to make sure the append query copies the correct field from the temporary to the actual table. very cool
Kelvin SparksCommented:
Regarding use of eclamation mark. You can take that further by selecting a range of cells in a sheet and naming as a range, then adding that name after the exclamation mark to restrict the import to that range rather than the entire sheet. I believe you can also use the A23:G25 style for this.
Shino_skayAuthor Commented:
I originally thought Capricorn's idea was to utilize a feature in Access but opening Excel from Access is a simple task which I didn't/couldn't think of.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.