none
DoCmd.TransferSpreadsheet acImport ... from specified workbook tab RRS feed

  • Question

  • Hi

    I use code to import excel data into access using this code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NYTTKGAUNDERLAG", strFolder3 & "NYTTKGAUNDERLAG" & ".xlsx", True

    It works fine. Data transferred is from "first" Tab of two in the spreadsheet. In this case the Tab with name "Rader".


    Now I want to import data from another Tab in the same workbook. Name of Tab is "Grupperat"

    Whats the code to get data from Tab "Grupperat"


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00



    Wednesday, November 4, 2015 10:14 AM

Answers

  • The transferspreadsheet method has a parameter named range, where you can specify from where are your data

    expression .TransferSpreadsheet(TransferTypeSpreadsheetTypeTableNameFileNameHasFieldNamesRangeUseOA)

    Range

    Optional

    Variant

    A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

    See an example below:

    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
                tablename:="tmpTableName", FileName:="SomeExcelFile", _
                Hasfieldnames:=False, Range:="WorkSheet!B1:B11"
                'This will import the range B1 through B11
                'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
                'format

    The reference about the method here:

    https://msdn.microsoft.com/pt-br/library/office/ff844793.aspx?f=255&MSPPError=-2147217396

    • Proposed as answer by André Santo Wednesday, November 4, 2015 12:00 PM
    • Marked as answer by ForssPeterNova Wednesday, November 4, 2015 12:21 PM
    Wednesday, November 4, 2015 12:00 PM

All replies

  • The transferspreadsheet method has a parameter named range, where you can specify from where are your data

    expression .TransferSpreadsheet(TransferTypeSpreadsheetTypeTableNameFileNameHasFieldNamesRangeUseOA)

    Range

    Optional

    Variant

    A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

    See an example below:

    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
                tablename:="tmpTableName", FileName:="SomeExcelFile", _
                Hasfieldnames:=False, Range:="WorkSheet!B1:B11"
                'This will import the range B1 through B11
                'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
                'format

    The reference about the method here:

    https://msdn.microsoft.com/pt-br/library/office/ff844793.aspx?f=255&MSPPError=-2147217396

    • Proposed as answer by André Santo Wednesday, November 4, 2015 12:00 PM
    • Marked as answer by ForssPeterNova Wednesday, November 4, 2015 12:21 PM
    Wednesday, November 4, 2015 12:00 PM
  • André

    Thanks it works just fine:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "NYTTKGAUNDERLAG", strFolder3 & "NYTTKGAUNDERLAG" & ".xlsx",_
    True, "Grupperat!A1:C1000"


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Wednesday, November 4, 2015 12:22 PM