Click here to Skip to main content
15,886,199 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
VB.NET
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 
Posted
Updated 11-Feb-11 7:32am
v2

1 solution

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.
 
Share this answer
 
v2
Comments
Yasser El Shazly 11-Feb-11 20:51pm    
does not work
Yusuf 12-Feb-11 8:08am    
That is not helpful. What does not work? simply changing a number is may not be enough. WHat effect did it had?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900