Sheet exists using Ms Access

[Tio_Chiel]

Dear All,

I am importing ms excel files from different locations
I would like to import all sheets but the number of sheets differ from file to file
the VBA function I am currently using :
---------------------------------------------------
Public Function GetPF()
Dim ReportFile, strDate As String, PStr As String
Dim Fstr1 As String, Fstr2 As String, Fstr3 As String, Fstr4 As String
Dim SStr1 As String, Sstr2 As String, Sstr3 As String, Sstr4 As String, Sstr5 As String
PStr = "D:\Data\Xls\CemisFiles\SC\PF\2008\"

Fstr1 = "PF-2008-Q1.xls"
Fstr2 = "PF-2008-Q2.xls"
Fstr3 = "PF-2008-Q3.xls"
Fstr4 = "PF-2008-Q4.xls"

SStr1 = "Sheet 1!A1:Z65536"
Sstr2 = "Sheet 1_1!A1:Z65536"
Sstr3 = "Sheet 1_2!A1:Z65536"
Sstr4 = "Sheet 1_3!A1:Z65536"
Sstr5 = "Sheet 1_4!A1:Z65536"

DoCmd.RunSQL "DELETE Import.* from Import"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr1, , SStr1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr1, , Sstr2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr1, , Sstr3

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr2, , SStr1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr2, , Sstr2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr2, , Sstr3

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr3, , SStr1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr3, , Sstr2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr3, , Sstr3

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr4, , SStr1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr4, , Sstr2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Import", PStr & Fstr4, , Sstr3

End Function
----------------------------------------------------------------

This works fine but I have to check myself every time how many sheets each file contains

What I would like to have is a extra code that checks if the sheet exists.
If exists it should import the sheet; if not resume to next commandline

Does anyone know how to tackle this ?

Thanks in advance,
Tio_Chiel

 

[DCrake]

You can chack to see if the sheet exists by using the Sheets.Count option. This will give you an indication as to howmany sheets exist in the workbook.

If the names of the indivdual worksheets are constant then you could also check by sheet name.

CodeMaster: