mardi 24 février 2015

Check if particular worksheet exists after choosing excel file in Application.FileDialog

I would like to check if the sheet named "Metadasheet" exist in excel file when choosen from File dailog.


My target steps are as follows: file dailog opens> select the excel file> check if the "Metadatasheet" exists> If "yes", perform operations>if "no" popup"choose the correct workbook". Following is the code(in access VBA), I would like to know, how and where do I put this check;





Public Function create(LatestSNR As String, Metadatasheet As String)
' LatestSNR is the name of the table or query you want to send to Excel
' Metadatasheet is the name of the sheet you want to send it to

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strFile As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

On Error GoTo err_handler
With Application.FileDialog(1) ' msoFileDialogOpen
.Filters.Clear
.Filters.Add "Excel workbooks (*.xls*)", "*.xls*"
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox "No workbook specified!", vbExclamation
Exit Function
End If
End With
Set rst = CurrentDb.OpenRecordset(LatestSNR)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strFile)

ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets(Metadatasheet)

xlWSh.Activate
xlWSh.Range("A2").Select

For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst

xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select

' selects all of the cells
ApXL.ActiveSheet.Cells.Select

' selects the first cell to unselect all cells
xlWSh.Range("A2").Select
rst.Close
Set rst = Nothing
Exit Function

err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function



Any suggestions are very helpful.Thanks in Advance!


Aucun commentaire:

Enregistrer un commentaire