Acceder a Access desde Excel mediante VBA

En un reciente curso de Microsoft Access 2010, los alumnos
–conocedores de mi preferencia por la programación- me pidieron que hiciera un
giro en el temario del curso y lo orientara hacia sus necesidades
fundamentales: enlazar bases de datos Access con libros Excel. Así, surgió la
necesidad de desarrollar un conjunto procedimientos (la gran mayoría de ellos
del tipo FUNCTION).

Para compartir esta información, pongo ese código a la
disposición de quién lo necesite.

‘ ===========================================================================

‘ Para
trabajar con ADO, se requiere añadir la referencia:

‘ Microsoft ActiveX Data Objects x.y Library

‘ Para
trabajar con ADOX,se requiere añadir la referencia a:

‘ Microsoft ADO Ext. x.y for DDL and Security

‘ Para
trabajar con Access.Application,se requiere añadir la referencia a:

‘ Microsoft Access x.y Object Library

‘ Para trabajar
con DAO, se requiere añadir la referencia a:

‘ Microsoft DAO x.y Object Library

‘ ============================================================================


============================================================================


============================================================================

‘ ============================================================================

Function ReadAccessTableToExcel(ByVal
pAccessDatabaseFullName As String, _

                             ByVal
pAccessTableName As String, _

                             ByVal
pExcelWorkbookFullName As String, _

                             ByVal pExcelTargetSheetName As
String) As Boolean

 

    Dim
oConnection  As ADODB.Connection

    Dim
oRecordset   As ADODB.Recordset

    Dim
oWorkbook    As Workbook

    Dim
oWorksheet   As Worksheet

    Dim i            As Integer

 

    On Error
GoTo TError

   

    Set
oConnection = New ADODB.Connection

   
oConnection.Open “Provider=Microsoft.Ace.OLEDB.12.0; ” & _

                    
“Data Source=” & pAccessDatabaseFullName

                     

    Set
oRecordset = New ADODB.Recordset

   
oRecordset.Open pAccessTableName, oConnection, _

                   
adOpenKeyset, adLockOptimistic, adCmdTable

 

 

    If
FileExist(pExcelWorkbookFullName) = False Then

       Set
oWorkbook = Application.Workbooks.Add

    Else

       Set oWorkbook =
Application.Workbooks.Open(pExcelWorkbookFullName)

    End If

   

    If
SheetExist(oWorkbook, pExcelTargetSheetName) = False Then

       Set
oWorksheet = oWorkbook.Worksheets.Add

      
oWorksheet.Name = pExcelTargetSheetName

    Else

       Set oWorksheet =
oWorkbook.Worksheets(pExcelTargetSheetName)

    End If

   
oWorksheet.UsedRange.Clear

   

    For i = 0 To
oRecordset.Fields.Count – 1

       
oWorksheet.Range(“A1”).Offset(, i).Value =
oRecordset.Fields(i).Name

    Next i

 

    oWorksheet.Range(“A1”).Resize(,
oRecordset.Fields.Count).Font.Bold = True

   
oWorksheet.Range(“A2”).CopyFromRecordset oRecordset

   

   
Application.DisplayAlerts = False

   

   
oWorkbook.SaveAs (pExcelWorkbookFullName)

   
oWorkbook.Close

   

    Application.DisplayAlerts
= True

   

   
oRecordset.Close

    Set
oRecordset = Nothing

   
oConnection.Close

    Set
oConnection = Nothing

   

   
ReadAccessTableToExcel = True

    Exit
Function

   

TError:

    If Not
(oRecordset Is Nothing) Then

       If
(oRecordset.State And adStateOpen) = adStateOpen Then oRecordset.Close

       Set
oRecordset = Nothing

    End If

    If
oConnection.State = adStateOpen Then oConnection.Close

   

   
ReadAccessTableToExcel = False

   

End Function

 

‘ ============================================================================


============================================================================

 

Function ReadAccessSQLToExcel(ByVal
pAccessDatabaseFullName As String, _

                            ByVal pSQLSentence As String, _

                            ByVal
pExcelWorkbookFullName As String, _

                
           ByVal pExcelTargetSheetName As
String) As Boolean

 

    Dim
oConnection As ADODB.Connection

    Dim
oRecordset  As ADODB.Recordset

    Dim
sConnection As String

    Dim
oWorkbook   As Workbook

    Dim
oWorksheet  As Worksheet

    Dim i           As Long

   

    On Error
GoTo TError

   

    Set
oConnection = CreateObject(“ADODB.Connection”)

   

    sConnection
= “Provider=Microsoft.ACE.OLEDB.12.0;” & _

                 
“Data Source=” & pAccessDatabaseFullName

   
oConnection.Open sConnection

   

    Set
oRecordset = oConnection.Execute(pSQLSentence)

   

    If
FileExist(pExcelWorkbookFullName) = False Then

       Set oWorkbook
= Application.Workbooks.Add

    Else

       Set
oWorkbook = Application.Workbooks.Open(pExcelWorkbookFullName)

    End If

   

    If
SheetExist(oWorkbook, pExcelTargetSheetName) = False Then

       Set
oWorksheet = oWorkbook.Worksheets.Add

       oWorksheet.Name = pExcelTargetSheetName

    Else

       Set
oWorksheet = oWorkbook.Worksheets(pExcelTargetSheetName)

    End If

   

    Set
oWorksheet = oWorkbook.Worksheets(pExcelTargetSheetName)

   
oWorksheet.UsedRange.Clear

   

    For i = 0 To
oRecordset.Fields.Count – 1

       
oWorksheet.Range(“A1”).Offset(, i).Value =
oRecordset.Fields(i).Name

    Next i

 

   
oWorksheet.Range(“A1”).Resize(,
oRecordset.Fields.Count).Font.Bold = True

   
oWorksheet.Range(“A2”).CopyFromRecordset oRecordset

 

    Application.DisplayAlerts
= False

   

   
oWorkbook.SaveAs (pExcelWorkbookFullName)

   
oWorkbook.Close

   

   
Application.DisplayAlerts = True

   

   
oRecordset.Close

    Set
oRecordset = Nothing

   
oConnection.Close

    Set
oConnection = Nothing

   

   
ReadAccessSQLToExcel = True

    Exit
Function

   

TError:

    If Not
(oRecordset Is Nothing) Then

       If
(oRecordset.State And adStateOpen) = adStateOpen Then oRecordset.Close

       Set
oRecordset = Nothing

    End If

    If
oConnection.State = adStateOpen Then oConnection.Close

   

   
ReadAccessSQLToExcel = False

   

End Function

 

Function GetAccessTableNames(ByVal
pAccessDatabaseFullName As String) _

                             As String()

 

    Dim
oConnection       As ADODB.Connection

    Dim
oCatalog          As ADOX.Catalog

    Dim
oTable            As ADOX.Table

    Dim
sConnectionString As String

    Dim
arrTableNames()   As String

    Dim i                 As Long

   

    On Error
GoTo TError

   

   
sConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” &
_

                       
“Data Source=” & pAccessDatabaseFullName

                           

    Set
oConnection = New ADODB.Connection

   
oConnection.Open sConnectionString

   

    Set oCatalog
= New ADOX.Catalog

    Set
oCatalog.ActiveConnection = oConnection

   

    i = -1

    For Each
oTable In oCatalog.Tables

       If oTable.Type =
“TABLE” Then

          i = i
+ 1

          ReDim
Preserve arrTableNames(i)

         
arrTableNames(i) = oTable.Name

       End If

    Next oTable

   

   
oConnection.Close

    Set oCatalog
= Nothing

    Set
oConnection = Nothing

   

   
GetAccessTableNames = arrTableNames

    Exit
Function

   

TError:

    If
oConnection.State = adStateOpen Then oConnection.Close

   

End Function

 

Function GetAccessTableFieldNames(ByVal
pAccessDatabaseFullName As String, _

                                  ByVal
pAccessTableName As String) As String()

 

    Dim
oConnection     As ADODB.Connection

    Dim
oRecordset      As ADODB.Recordset

    Dim arrFieldNames()
As String

    Dim i               As Long

   

    On Error
GoTo TError

   

    Set
oConnection = New ADODB.Connection

   
oConnection.Open “Provider=Microsoft.Ace.OLEDB.12.0; ” & _

                    
“Data Source=” & pAccessDatabaseFullName

                     

    Set
oRecordset = New ADODB.Recordset

   
oRecordset.Open pAccessTableName, oConnection, _

                   
adOpenKeyset, adLockOptimistic, adCmdTable

   

    For i = 0 To
oRecordset.Fields.Count – 1

        ReDim
Preserve arrFieldNames(i)

       
arrFieldNames(i) = oRecordset.Fields(i).Name

    Next i

   

   
oRecordset.Close

    Set
oRecordset = Nothing

   
oConnection.Close

    Set
oConnection = Nothing

   

   
GetAccessTableFieldNames = arrFieldNames

    Exit Function

   

TError:

    If Not
(oRecordset Is Nothing) Then

       If
(oRecordset.State And adStateOpen) = adStateOpen Then oRecordset.Close

       Set
oRecordset = Nothing

    End If

    If
oConnection.State = adStateOpen Then oConnection.Close

   

End Function

 

Function GetAccessTableFieldTypes(ByVal
pAccessDatabaseFullName As String, _

                                 ByVal
pAccessTableName As String) As String()

 

    Dim
oConnection      As ADODB.Connection

    Dim
oRecordset       As ADODB.Recordset

    Dim arrFieldTypes()  As String

    Dim i                As Long

 

    On Error
GoTo TError

   

    Set
oConnection = New ADODB.Connection

   
oConnection.Open “Provider=Microsoft.Ace.OLEDB.12.0; ” & _

                    
“Data
Source=” & pAccessDatabaseFullName

                     

    Set
oRecordset = New ADODB.Recordset

   
oRecordset.Open pAccessTableName, oConnection, _

                   
adOpenKeyset, adLockOptimistic, adCmdTable

   

    For i = 0 To
oRecordset.Fields.Count – 1

        ReDim Preserve arrFieldTypes(i)

       
arrFieldTypes(i) = TypeName(oRecordset.Fields(i).Value)

    Next i

   

    oRecordset.Close

    Set
oRecordset = Nothing

   
oConnection.Close

    Set
oConnection = Nothing

   

   
GetAccessTableFieldTypes = arrFieldTypes

    Exit
Function

   

TError:

    If Not
(oRecordset Is Nothing) Then

       If
(oRecordset.State And adStateOpen) = adStateOpen Then oRecordset.Close

       Set
oRecordset = Nothing

    End If

    If
oConnection.State = adStateOpen Then oConnection.Close

   

End Function

 

Function GetAccessTableFieldValues(ByVal
pAccessDatabaseFullName As String, _

                                   ByVal
pAccessTableName As String, _

                                   ByVal
pFieldName As String) As Variant()

 

    Dim
oConnection      As ADODB.Connection

    Dim
oRecordset       As ADODB.Recordset

    Dim
arrFieldValues() As Variant

    Dim i                As Long

 

    On Error
GoTo TError

   

    Set
oConnection = New ADODB.Connection

   
oConnection.Open “Provider=Microsoft.Ace.OLEDB.12.0; ” & _

                    
“Data Source=” & pAccessDatabaseFullName

                     

    Set
oRecordset = New ADODB.Recordset

   
oRecordset.Open pAccessTableName, oConnection, _

                   
adOpenKeyset, adLockOptimistic, adCmdTable

   

    ReDim
arrFieldValues(oRecordset.RecordCount – 1)

   

    i = -1

    Do While Not
oRecordset.EOF

       i = i + 1

      
arrFieldValues(i) = oRecordset.Fields(pFieldName).Value

      
oRecordset.MoveNext

    Loop

   

   
oRecordset.Close

    Set
oRecordset = Nothing

   
oConnection.Close

    Set
oConnection = Nothing

   

   
GetAccessTableFieldValues = arrFieldValues

    Exit
Function

   

TError:

    If Not
(oRecordset Is Nothing) Then

       If (oRecordset.State
And adStateOpen) = adStateOpen Then oRecordset.Close

       Set
oRecordset = Nothing

    End If

    If
oConnection.State = adStateOpen Then oConnection.Close

 

End Function

 

Function GetAccessQueryNames_1(ByVal
pAccessDatabaseFullName As String) _

                               As String()

 

    Dim
sConnectionString As String

    Dim
oConnection       As ADODB.Connection

    Dim
oCatalog          As ADOX.Catalog

    Dim
oView             As ADOX.View

    Dim
arrQueryNames()   As String

    Dim i                 As Long

 

    On Error
GoTo TError

 

   
sConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” &
_

                       
“Data Source=” & pAccessDatabaseFullName

 

    Set
oConnection = New ADODB.Connection

   
oConnection.Open sConnectionString

 

    Set oCatalog
= New ADOX.Catalog

    Set
oCatalog.ActiveConnection = oConnection

 

    i = -1

    For Each
oView In oCatalog.Views

       i = i + 1

       ReDim
Preserve arrQueryNames(i)

      
arrQueryNames(i) = oView.Name

    Next oView

   

   
oConnection.Close

    Set oCatalog
= Nothing

    Set
oConnection = Nothing

   

    GetAccessQueryNames_1 =
arrQueryNames

    Exit
Function

   

TError:

    If
oConnection.State = adStateOpen Then oConnection.Close

   

End Function

 

Function GetAccessQueryNames_2(ByVal
pAccessDatabaseFullName As String) As String()

 

    Dim
oDatabase       As DAO.Database

    Dim
oQueryDef       As DAO.QueryDef

    Dim
arrQueryNames() As String

    Dim i               As Long

 

    On Error
GoTo TError

   

    Set oDatabase =
DBEngine.OpenDatabase(pAccessDatabaseFullName)

   

    i = -1

    For Each
oQueryDef In oDatabase.QueryDefs

       If
Mid(oQueryDef.Name, 1, 1) <> “~” Then

          i = i
+ 1

          ReDim
Preserve arrQueryNames(i)

         
arrQueryNames(i) = oQueryDef.Name

       End If

    Next

   

   
oQueryDef.Close

    Set
oQueryDef = Nothing

   
oDatabase.Close

    Set
oDatabase = Nothing

   

   
GetAccessQueryNames_2 = arrQueryNames

    Exit
Function

   

TError:

    On Error
Resume Next

    oQueryDef.Close

    On Error
Resume Next

   
oDatabase.Close

   

End Function

 

Function GetAccessQuerySELECTFromQueryName(ByVal
pAccessDatabaseFullName As String, _

                                   ByVal
pAccessQueryName As String) As String

   

    Dim
oDatabase As DAO.Database

    Dim
oQueryDef As DAO.QueryDef

      

    On Error
GoTo TError

      

    Set
oDatabase = DBEngine.OpenDatabase(pAccessDatabaseFullName)

   

    Set
oQueryDef = oDatabase.QueryDefs(pAccessQueryName)

   

    GetAccessQuerySELECTFromQueryName
= oQueryDef.Sql

   

   
oDatabase.Close

    Set
oDatabase = Nothing

   

    Exit
Function

   

TError:

    On Error
Resume Next

   
oDatabase.Close

   

   
GetAccessQuerySELECTFromQueryName = “”

   

End Function

 

Function ExecuteAccessQueryName(ByVal
pAccessDatabaseFullName As String, _

                  
       ByVal pAccessQueryName As String) As
ADODB.Recordset

   

    Dim
sConnectionString As String

    Dim
oConnection       As ADODB.Connection

    Dim
oRecordset        As ADODB.Recordset

    Dim
sSQL              As String

  

    On Error
GoTo TError

 

   
sConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” &
_

                       
“Data Source=” & pAccessDatabaseFullName

 

    Set
oConnection = New ADODB.Connection

   
oConnection.Open sConnectionString

   

    sSQL =
GetAccessQuerySELECTFromQueryName(pAccessDatabaseFullName, _

                                            
pAccessQueryName)

   

    Set
oRecordset = oConnection.Execute(sSQL)

                                     

    Set
ExecuteAccessQueryName = oRecordset

   

    Set
oRecordset = Nothing

    Exit
Function

   

TError:

    On Error
Resume Next

    Set
oRecordset = Nothing

   

    Set
ExecuteAccessQueryName = Nothing

   

End Function

 

Function ExecuteAccessQuerySELECT(ByVal
pAccessDatabaseFullName As String, _

                       
ByVal pAccessQuerySELECT As String) As ADODB.Recordset

   

    Dim
sConnectionString As String

    Dim
oConnection       As New ADODB.Connection

    Dim
oRecordset        As New ADODB.Recordset

    Dim
oCommand          As New ADODB.Command

  

    On Error
GoTo TError

   

   
sConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” &
_

                       
“Data Source=” & pAccessDatabaseFullName

 

   
oConnection.Open sConnectionString

   

   
oCommand.CommandType = adCmdText

   
oCommand.CommandText = pAccessQuerySELECT

   
oCommand.ActiveConnection = oConnection

 

    Set
oRecordset = oCommand.Execute()

   

    Set ExecuteAccessQuerySELECT
= oRecordset

   

    Set oCommand
= Nothing

    Set
oRecordset = Nothing

   
oConnection.Close

    Set
oConnection = Nothing

   

    Exit
Function

   

TError:

    If Not
(oRecordset Is Nothing) Then

       If
(oRecordset.State And adStateOpen) = adStateOpen Then oRecordset.Close

       Set
oRecordset = Nothing

    End If

    If
oConnection.State = adStateOpen Then oConnection.Close

 

    Set
ExecuteAccessQuerySELECT = Nothing

   

End Function

 

Function ImportExportAccessTableFromToExcel(ByVal
pImportExportLink As String, _

                                    ByVal
pAccessDatabaseFullName As String, _

                                    ByVal
pAccessTableName As String, _

                                    ByVal pExcelWorkbookFullName, _

                                    ByVal
pExcelSheetName As String, _

                         Optional ByVal
pExcelSheetFieldNamesRow As Boolean, _

                         Optional ByVal pRange
As Variant) As Boolean

 

‘—————————————————————————–

‘You can use the TransferSpreadsheet method to import
or export data between the

‘current Access database and a spreadsheet file.

‘You can also link the data in a Microsoft Excel
spreadsheet to the current

‘Access database. With a linked spreadsheet, you can
view and edit the

‘spreadsheet data with Access while still allowing
complete access to the data

‘from your Excel spreadsheet program.

‘Sintaxis:

‘expression.TransferSpreadsheet(TransferType,
SpreadsheetType, TableName, _

‘                               Filename,
HasFieldNames, Range, UseOA)

‘expression A variable that represents a DoCmd object.

‘Parameters

‘Name              
Required/Optional   Data Type           Description

‘—-              
—————–   ———           ———–

‘TransferType      
Optional           
AcDataTransferType  The type of
transfer

‘                                                          
you want to make.

‘                                                           The
default value is

‘                                                          
acImport.

‘SpreadsheetType   
Optional           
AcSpreadSheetType   The type of
spreadsheet

‘                                                           to import from,
export

‘                                                          
to, or link to.

‘TableName         
Optional            Variant             A string expression

‘                                                 
         that is the name of

‘                                                          
the Microsoft Office

‘                                                          
Access table you want

‘                                                          
to import spreadsheet

‘                                                          
data into, export

‘                                                          
spreadsheet data from,

‘                                                          
or link spreadsheet

‘                                                          
data to, or the Access

‘                                                          
select query whose

‘                                                          
results you want to

‘                                                           export
to a spreadsheet.

‘Filename          
Optional            Variant             A string expression that’s

‘                                                          
the file name and path of

‘                                                           the
spreadsheet you want to

‘                                                          
import from, export to, or link to.

‘HasFieldNames     
Optional            Variant             Use True (–1) to use the first row

‘                                                          
of the spreadsheet as field names when

‘                                                          
importing or linking.

‘                                                          
Use False (0) to treat the first row

‘                                                          
of the spreadsheet as normal data.

‘                                                          
If you leave this argument blank, the

‘                                                          
default (False) is assumed. When you

‘                                                          
export Access table or select query data

‘                                                        
  to a spreadsheet, the field
names are

‘                                                          
inserted into the first row of the

‘                                                          
spreadsheet no matter what you enter for

‘                  
                                        this
argument.

‘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.

‘UseOA             
Optional            Variant             This argument is not supported.

‘AcDataTransferType:

‘Specifies the type of transfer that you want to make
with the TransferDatabase

‘or TransferSpreadsheet method.

‘Name                       Value           Description

‘—-                       —–           ———–

‘acExport                   1               The data is exported.

‘acImport                   0               (Default) The data is imported.

‘acLink                     2               The database is linked to the
specified data source.

‘Remarks

‘Note

‘The acLink transfer type is not supported for
Microsoft Access projects (.adp).

‘AcSpreadSheetType:

‘Name           
           Value           Description

‘—-                       —–           ———–

‘acSpreadsheetTypeExcel3    0               Microsoft Excel 3.0 format

‘acSpreadsheetTypeExcel4    6               Microsoft Excel 4.0 format

‘acSpreadsheetTypeExcel5    5               Microsoft Excel 5.0 format

‘acSpreadsheetTypeExcel7    5               Microsoft Excel 95 format

‘acSpreadsheetTypeExcel8    8               Microsoft Excel 97 format

‘acSpreadsheetTypeExcel9    8               Microsoft Excel 2000 format

‘acSpreadsheetTypeLotusWJ2  4               Japanese version only

‘acSpreadsheetTypeLotusWK1  2               Lotus 1-2-3 WK1 format

‘acSpreadsheetTypeLotusWK3  3               Lotus 1-2-3 WK3 format

‘acSpreadsheetTypeLotusWK4  7               Lotus 1-2-3 WK4 format

‘When required to export from Access 2010 to Excel
2010 use docmd instead of

‘DoCmd.TransferSpreadsheet

‘DoCmd.OutputTo acOutputQuery,
“YourTableName/QueryName here”, _

‘                             
“ExcelWorkbook(*.xlsx)”, “Excel FileName.xlsx”, True

‘————————————-——————————————–

 

    Dim
oAccessApplication As Access.Application

    Dim
oRange             As Range

      

    On Error
GoTo TError

       

    Set oAccessApplication
= New Access.Application

   
oAccessApplication.OpenCurrentDatabase pAccessDatabaseFullName

           

    Dim
oWorksheet As Worksheet

    Set
oWorksheet = ThisWorkbook.Worksheets(pExcelSheetName)

   

    If Not
IsMissing(pRange) Then Set oRange = oWorksheet.Range(pRange)

       

    Select Case
UCase(pImportExportLink)

       Case
“I”

          If
IsMissing(pRange) Then

            
oAccessApplication.DoCmd.TransferSpreadsheet acImport, _

                                                          acSpreadsheetTypeExcel12Xml, _

                                                         
pAccessTableName, pExcelWorkbookFullName, _

                                                         
pExcelSheetFieldNamesRow

          Else

             oAccessApplication.DoCmd.TransferSpreadsheet
acImport, _

                                                         
acSpreadsheetTypeExcel12Xml, _

                                                         
pAccessTableName, pExcelWorkbookFullName, _

                                                          pExcelSheetFieldNamesRow,
pRange

          End If

         

       Case
“E”

         
oAccessApplication.DoCmd.TransferSpreadsheet acExport, _

                                                      
acSpreadsheetTypeExcel12Xml,
_

                                                      
pAccessTableName, pExcelWorkbookFullName

                                                     

       Case
“L”

         
oAccessApplication.DoCmd.TransferSpreadsheet acLink, _

                                                      
acSpreadsheetTypeExcel12Xml, _

                                                      
pAccessTableName, pExcelWorkbookFullName, _

                                                      
pExcelSheetFieldNamesRow, pRange

    End Select

   

    oAccessApplication.Quit

    ImportExportAccessTableFromToExcel = True

   

    Exit
Function

   

TError:

   
ImportExportAccessTableFromToExcel = False

 

End Function

 

Function AccessDatabaseBackUp(ByVal pAccessSourceDatabaseFullName
As String, _

                              ByVal
pAccessTargetDatabaseFullName As String) As Boolean

  

   On Error GoTo
TError

  

   FileCopy
pAccessSourceDatabaseFullName, pAccessTargetDatabaseFullName

  

  
AccessDatabaseBackUp = True

   Exit Function

  

TError:

   AccessDatabaseBackUp
= False

 

End Function

 

‘ ============================================================================

‘ ============================================================================

Function IsVarArrayEmpty(ByVal pArray As Variant)

 

    Dim i As
Long

   

    On Error
Resume Next

    i =
UBound(pArray, 1)

    If
Err.Number = 0 Then

        IsVarArrayEmpty = False

    Else

       
IsVarArrayEmpty = True

    End If

 

End Function

 

Function FileExist(ByVal pFileFullName As String) As
Boolean

 

   If
Dir(pFileFullName) = “” Then

      FileExist
= False

   Else

      FileExist
= True

   End If

 

End Function

 

Function SheetExist(ByVal pWorkbook As Workbook, ByVal
pSheetName As String) As Boolean

 

   Dim oHoja As
Worksheet

  

   For Each
oHoja In pWorkbook.Worksheets

      If
UCase(oHoja.Name) = UCase(pSheetName) Then

        
SheetExist = True

         Exit
Function

      End If

   Next

   SheetExist =
False

  

End Function

 

‘ ============================================================================


============================================================================

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *