[AccessD] A2003:Remove first line in CSV/Excel file

MartyConnelly martyconnelly at shaw.ca
Tue Oct 24 10:03:16 CDT 2006


Use ADO with the Jet OLE DB Provider

With Excel workbooks, the first row in a range is considered to be the 
header row (or field names) by default. If the first range does not 
contain headers, you can specify HDR=NO in the extended properties in 
your connection string. If the first row does not contain headers, the 
OLE DB provider automatically names the fields for you (where F1 would 
represent the first field, F2 would represent the second field, and so 
forth).
Other Jet Provider Connection Settings

Column headings: By default, it is assumed that the first row of your 
Excel data source contains columns headings that can be used as field 
names. If this is not the case, you must turn this setting off, or your 
first row of data "disappears" to be used as field names. This is done 
by adding the optional HDR= setting to the Extended Properties of the 
connection string. The default, which does not need to be specified, is 
HDR=Yes. If you do not have column headings, you need to specify HDR=No; 
the provider names your fields F1, F2, etc. Because the Extended 
Properties string now contains multiple values, it must be enclosed in 
double quotes itself, plus an additional pair of double quotes to tell 
Visual Basic to treat the first set of quotes as literal values, as in 
the following example (where extra spaces have been added for visual 
clarity). .
ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "

ExcelADO.exe demonstrates how to use ADO to read and write data in Excel 
workbooks
http://support.microsoft.com/?kbid=278973

or you can use DAO
     Dim dbtmp As DAO.Database
     Dim tblObj As DAO.TableDef
      Dim rs As DAO.Recordset

      Set dbtmp = OpenDatabase_
           ("<complete path>\test.xls", False, True, "Excel 8.0;")

      DoEvents
'  Note the reverse apostrophe "`" while specifying the range
'      name(myrange2).
      Set rs = dbtmp.OpenRecordset("select * from `myRange2`")
      While Not rs.EOF
          For x = 0 To rs.Fields.Count - 1
              Debug.Print rs.Fields(x).Value
          Next
          rs.MoveNext
      Wend
      End Sub

      Note the reverse apostrophe "`" while specifying the range
      name(myrange2).

Darren DICK wrote:

>Further to my last
> 
>Does anyone have any code that will remove the first line in an Excel doc but
>from Access?
> 
>Many thanks again in advance
> 
>Darren
> 
>
>  _____  
>
>From: Darren DICK [mailto:darrend at nimble.com.au] 
>Sent: Tuesday, 24 October 2006 11:55 PM
>To: 'Access Developers discussion and problem solving'
>Subject: A2003:Rename a file in a folder
>
>
>Hi all
> 
>I have a file that will be always named the same - EG myCoolCSVfile.csv
> 
>It will be dumped into a folder...say C:\MyCoolFolder at random times
> 
>I need some code to Check the C:\myCoolFolder to see if the file
>myCoolCSVfile.csv file exists in the folder - and if it does to rename it to
>YYMMDDHHMMSS.csv
> 
>Where YY = Year MM = Month DD = Day etc etc
> 
>Does anyone have anything that might suit?
> 
>Many thanks in advance
> 
>Darren
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the AccessD mailing list