Databases
StackOverflow2013
Postsdbo
POHow can I programmatically import Excel data into an Access table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I programmatically import Excel data into an Access table?
    primarykey
    data
    text
    <p>I've read through a bit of the related threads, but still left me with this question. I want to write a function in an Access database application to programmatically import Excel data starting before the first two rows—which are the header and the unit delimiters.</p> <p>I am looking to accomplish the following things:</p> <ul> <li>Being able to dynamically select the Excel file I am looking to import, perhaps using a dialog box and perhaps a file browser window.</li> <li>Insert 'common' data into each row as it's imported - like the asset number of the recorder and the recorder's designated location.</li> <li>Start the import at row #3, instead of row #1 - as the device automatically puts the header and unit of measurement information for the record up there.</li> <li>Ignore all other columns in the worksheet - the data will ALWAYS be present in columns A through G, and data will ALWAYS begin on row #3.</li> </ul> <p>This is how the Excel data is commonly formatted (the dashes represent the data):</p> <pre> Date Time Temp Dew Point Wet Bulb GPP RH Cº Cº Cº g/Kg % ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- </pre> <p>I've tried the built-in Access 'Get External Data' function, but it won't skip beyond row #2 and the extra data in the Excel file throws an error when trying to import, stopping the process in its tracks.</p> <p>I'll be the first to admit that I have never tried to write a import function for Access before using external files, hence I am a bit of a newbie. Any help people can show me will always be greatly appreciated, and I can update this with attempted code as necessary. Thank you in advance for all of your help, everyone!</p> <p>-- Edited 01/03/2011 @ 10:41 am --</p> <p>After reading the ADO connection to Excel data thread proposed by Remou, here is some code I think <strong>might</strong> do the job, but I am not sure.</p> <pre><code>Dim rs2 As New ADODB.Recordset Dim cnn2 As New ADODB.Connection Dim cmd2 As New ADODB.Command Dim intField As Integer Dim strFile As String strFile = fncOpenFile If strFile = "" Then Exit Sub With cnn2 .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source='" &amp; strFile &amp; "'; " &amp; "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'" .Open End With Set cmd2.ActiveConnection = cnn2 cmd2.CommandType = adCmdText cmd2.CommandText = "SELECT * FROM [Data$] WHERE G1 IS NOT NULL" rs2.CursorLocation = adUseClient rs2.CursorType = adOpenDynamic rs2.LockType = adLockOptimistic rs2.Open cmd2 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USComrad_Durandal
    1. USComrad_Durandal
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I programmatically import Excel data into an Access table?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I programmatically import Excel data into an Access table?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow can I programmatically import Excel data into an Access table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload