• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

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

  • 5
  • 3
  • 2
3 Solutions
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.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now