[AccessD] Reading Data from Excel into Access Where the Excel Worksheet Name Changes

Brad,

Off the top of my head I can think of two ways I have done something in the
past.

  1.  Make a copy of the Excel workbook and change the name of the specific
worksheet to the name hardcoded in the VBA
  2.  We used to have an Excel sheet from which we had to import data, the
sheet would always be in the same position but the sheet name would change,
so I would use code similar the the one below:

Function GetSheetNames()
    Dim xlsApp As Excel.Application
    Dim xlsWbk As Excel.Workbook
    Dim xlsSht As Excel.Worksheet

    Dim strFile As String
    Dim strSheet As String

    strFile = "YourExcelFileName.xls"

    Set xlsApp = New Excel.Application
    Set xlsWbk = xlsApp.Workbooks.Open(strFile)

    Set xlsSht = xlsWbk.Worksheets(2) ' or whatever position the sheet is
usually in
    strSheet = xlsSht.Name

    xlsWbk.Close
    Set xlsWbk = Nothing
    xlsApp.Quit
    Set xlsApp = Nothing

    ' Code here to use the actual sheet name

End Function

Paul



On 1 April 2013 22:20, Brad Marks <BradM at blackforestltd.com> wrote:

> All,
>
> We have an Access 2007 application that pulls data from several sources,
> including an Excel file.
>
> There are several worksheets in the Excel file, but data is only pulled
> from one worksheet.
>
> There is some complexity in the current process because the name of the
> worksheet changes.  We do not have control over this name.
>
> I have been thinking about a better/different approach.
>
> Is there some way in Access VBA code to change the name of an Excel
> Worksheet?  This step could be done prior to the main processing.  We
> could make a copy of the master Excel file and then work with our own
> copy.
>
> I am curious if others have run into this issue when working with Excel
> files and I am curious if there are ideas on how to better deal with
> this.
>
> Thanks,
> Brad
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com