ImportExportSpreadsheet Macro

[Dundee Lad]

Hi Board, I am using access 2013. I am trying to get the ImportExportSpreadsheet Macro to import one of the sheets in a work book. The sheet tab name changes but will always be 'Sheet2'. Do you know what I input into the range field to import this?

 

[Dundee Lad]

hi board, any ideas? I have searched everywhere and no joy

 

[mole999]

what code have you got so far, pop in here between code tags

 

[Dundee Lad]

no code, I am using the macro builder, although happy to use code if possible

 

[mole999]

macros are VBA code, would be useful to see what you have recorded already

 

[Dundee Lad]

ok just learnt something new!

Option Compare Database

'------------------------------------------------------------
' Mcr_ImportCosts2
'
'------------------------------------------------------------
Function Mcr_ImportCosts2()
On Error GoTo Mcr_ImportCosts2_Err

    On Error Resume Next
    DoCmd.Hourglass True
    DoCmd.DeleteObject acTable, "Tbl_Costs2"
    DoCmd.TransferSpreadsheet acImport, 10, "Tbl_Costs2", "Q:\Production Database\Tables\Tbl_Costs\2016_17 YTD.xlsx", True, "P1 Capex!"
    DoCmd.OpenQuery "Qry_App_TblCosts2_Tbl_Costs", acViewNormal, acEdit
    DoCmd.Hourglass False


Mcr_ImportCosts2_Exit:
    Exit Function

Mcr_ImportCosts2_Err:
    MsgBox Error$
    Resume Mcr_ImportCosts2_Exit

End Function

 

[xenou]

macros are VBA code, would be useful to see what you have recorded already

In Access technically macros (Macros capital M) are a feature that uses a gui designer to build custom sets of automated actions and the user does not work directly with vba code. Just an FYI. This can be confusing at times especially for those who come to Access with some Excel vba experience.

Note, and of course now the OP has named his VBA code function a macro lol!

 

[mole999]

In Access technically macros (Macros capital M) are a feature that uses a gui designer to build custom sets of automated actions and the user does not work directly with vba code. Just an FYI. This can be confusing at times especially for those who come to Access with some Excel vba experience.
Note, and of course now the OP has named his VBA code function a macro lol!

OK common technicality of words (I'm common )
Visual Basic for Applications and Excel window is Microsoft Visual Basic and we all know most of those are "macros" in common parlance

 

[mole999]

This should answer the specific question about importing sheet 2 > Import a particular excel sheet from list of sheets in workbook using Access VBA - Access World Forums

 

[Dundee Lad]

I amended the sheet tab name from P1 Capex! to Sheets(2) in the range field but get an error telling me it doesn't recognise