Блог

Я хочу экспортировать 2 листа (ACTIVITY и МАСТЕР-ФАЙЛ) из Excel для доступа с помощью таблицы учетных записей и таблицы активности с помощью AccountDoCmd.Функция TransferSpreadsheet.

Моя проблема сейчас в том, что по какой-то причине он экспортирует содержимое ДЕЙСТВИЯ в обе таблицы базы данных Access.

Я пробовал различные методы, такие как активация электронной таблицы МАСТЕР-ФАЙЛА в начале подраздела экспорта и скрытие электронной таблицы ДЕЙСТВИЙ после ее экспорта, но она по-прежнему экспортирует содержимое ДЕЙСТВИЙ в обе таблицы.

 'exports the data from the spreadsheet to the Access database
Sub ExportMaster()

    'declaring our files, directories, tables and field names
    Dim strPathFile As String, strFile As String, strPath As String, fileName As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    Dim MasterDB As Worksheet
    
    'Declaring year value of 1 month amp; 2 month
    'This is important to compare datasets from 2 months ago amp; last month
    Year_1M = Format(Date - 27, "YYYY")

    'Declaring month value of 1 month amp; 2 month
    'This is important to compare datasets from 2 months ago amp; last month
    Month_1M = Format(Date - 27, "MM")

    'This translates the current month from number to character format
    MonthChar_1 = MonthName(Month_1M, False)
    
    sheet = "MASTERFILE_" amp; Year_1M amp; Month_1M
    
    Application.DisplayAlerts = False
    
    blnHasFieldNames = True
    
    strPath = "Z:Danny Tool Test Folder"
    
    strTable = "AccountTable"
    
    strFile = Dir(strPath amp; "Monthly Reporting Tool.xlsm")
      strPathFile = strPath amp; strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames, sheet
    
    
End Sub

'exports the data from the spreadsheet to the Access database
Sub ExportActivity()
    'declaring our files, directories, tables and field names
    Dim strPathFile As String, strFile As String, strPath As String, fileName As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    Dim MasterDB As Worksheet
    
    'Declaring year value of 1 month amp; 2 month
    'This is important to compare datasets from 2 months ago amp; last month
    Year_1M = Format(Date - 27, "YYYY")

    'Declaring month value of 1 month amp; 2 month
    'This is important to compare datasets from 2 months ago amp; last month
    Month_1M = Format(Date - 27, "MM")

    'This translates the current month from number to character format
    MonthChar_1 = MonthName(Month_1M, False)
    
    sheet = "ACTIVITY_" amp; Year_1M amp; Month_1M
    
    Application.DisplayAlerts = False
    
    blnHasFieldNames = True
    
    strPath = "Z:Danny Tool Test Folder"
    
    strTable = "ActivityTable"
    
    strFile = Dir(strPath amp; "Monthly Reporting Tool.xlsm")
      strPathFile = strPath amp; strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames, sheet
    
    
End Sub