Solved

Importing from Excel - only 1 tab

Posted on 2014-10-30
2
220 Views
Last Modified: 2014-10-30
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
Comment
Question by:pdvsa
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40414215
what happen if you change

strName = xlWB.Sheets(1).Name

with

strName = "Deliverables"
0
 

Author Closing Comment

by:pdvsa
ID: 40414439
perfect.  thank you.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Input box criteria 3 20
Run MS Access Module from Macro / Bat File 9 47
Sending Attachment via CDO 3 22
Syntax using Declare 3 16
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now