将Excel工作表导入Access db时保留第一条记录 [英] Leave the First record while importing Excel sheet into Access db

问题描述

我开发了一个代码，将Excel文件导入到Access数据库表中，但是将Excel中的字段名称视为Access中的记录或数据记录，所以我想解决这个问题，我只想在将其导入int时保留First记录访问这是代码

I develop a code that take the Excel file and import the data in the access db table but it treat with the name of fields in Excel as a record or a data record in access and i want to solve that Simply i want to Leave the First record while i import it int Access this is the code

Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   Dim Path As String
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      ' Allow user to make multiple selections in dialog box
      fDialog.AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      fDialog.Title = "Please Select An Excel Sheet"

      ' Clear out the current filters, and add our own.
      fDialog.Filters.Clear
      fDialog.Filters.Add "Excel Sheets 2003", "*.xls"
      fDialog.Filters.Add "Excel Sheets 2007", "*.xlsx"
      '.Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If fDialog.Show = True Then
       'fDialog.SelectedItems
       Path = fDialog.SelectedItems(1)
       
      'Importing the Data From Excel Sheet to access database
 
      Dim WrksheetName As String
      Dim i As Integer
      Dim xl As Object
      Set xl = CreateObject("Excel.Application")

      xl.Visible = True
      xl.Workbooks.Open Path
      With xl
      .Visible = True
      With .Workbooks(.Workbooks.Count)
      For i = 1 To .Worksheets.Count
      WrksheetName = .Worksheets(i).Name
      DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, Path
      Next i
      End With
      End With
      Set xl = Nothing
      Me.txt_Path.SetFocus
      Me.txt_Path.Text = Path
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With 

推荐答案

我不确定您的问题是什么.是否要在Excel中保留第一行?如果是这样，如果您进行更改会发生什么

For i = 1 To .Worksheets.Count
到
For i = 2 To .Worksheets.Count

我不是访问开发人员，可能是基于1的索引而不是基于0的索引.

I am not sure what is your question. Do you want leave the first row in Excel behind? If so what happens if you change

For i = 1 To .Worksheets.Count
to
For i = 2 To .Worksheets.Count

I am not an access developer, may be it is 1-based as opposed to 0-based index.

这篇关于将Excel工作表导入Access db时保留第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！