Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Importing from Excel - only 1 tab

Experts,

I have an excel file with numerous tabs.
The tab I want to import is the first tab named "Deliverables"

I have code to import.
I found that in order to import with this code I can not have >1 tab.

What part of the code do I need to change in order to import the first tab of a workbook that has many tabs?  

fyi:  the import worked fine when I created another workbook with only the first tab being populated and no other tabs existed (not even the default Sheet2, Sheet3)

Private Sub cmdImport_Click()

    Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
        strName = xlWB.Sheets(1).Name
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing
   DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"

End Sub

Open in new window

0
pdvsa
Asked:
pdvsa
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what happen if you change

strName = xlWB.Sheets(1).Name

with

strName = "Deliverables"
0
 
pdvsaProject financeAuthor Commented:
perfect.  thank you.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now