AutoIncrementing a Column like a database in Excel 2007

[keith]

Hi,
We're going to use an SQLite database but we'd like to do all our data
entry in excel. Then we'd like to save the sheets as .csv files and import
the data into an SQLite database. For us to do this successfully we need to
be able to AutoIncrement an id field in Excel like a database would. Does
anyone know the best way to do this?

Thanks,
Keith

 

[Dick Kusleika]

We're going to use an SQLite database but we'd like to do all our data
entry in excel. Then we'd like to save the sheets as .csv files and import
the data into an SQLite database. For us to do this successfully we need to
be able to AutoIncrement an id field in Excel like a database would. Does
anyone know the best way to do this?

So you need to get the next available unique number from the SQLite
database? This example uses Access, so you'll have to change the connection
string to use the SQLite driver.

Public Function GetNextIndex()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sCn As String, sSql As String

sCn = "DSN=MS Access Database;DBQ=C:\service\service.mdb;"
sSql = "SELECT MAX(index) FROM Parts_Used"

Set cn = New ADODB.Connection
cn.Open sCn
Set rs = cn.Execute(sSql)

If Not (rs.BOF And rs.EOF) Then
GetNextIndex = rs.Fields(0).Value + 1
Else
GetNextIndex = 1
End If

End Function

 

[Internetdomainowner]

Hi,
  We're going to use an SQLite database but we'd like to do all our data
entry in excel. Then we'd like to save the sheets as .csv files and import
the data into an SQLite database. For us to do this successfully we need to
be able to AutoIncrement an id field in Excel like a database would. Does
anyone know the best way to do this?

Thanks,
   Keith

Try this...

Use Range A1 as your starting posistion and enter the following
formula...
=IF(LEN(B1)=0, "", 1)

In Range A2 and the rest of the other cells...
=IF(LEN(B2)=0, "", SUM(A1+1))

Copy the formula from Range A2 and paste all the way down the bottom
of the sheet... when you export the file out it will come out clean
like a database with an Access style primary key.

 

[keith]

Hi we planned on recreating the database tables each time through a simple
export process. So I really need Excel to manage the AutoIncrement. So we
just save the excel sheet as an .csv file and then import that into an Excel
table. So I would like to find a way to solve this in excel and not have any
knowledge of our database.

Thanks,
Keith

 

[keith]

Hi,
This won't work for us because of relation ship issues. We will have 2
excel sheets Excel Sheet 2 with have a relational ID back to Excel Sheet 1.
So if you insert a row inbetween already generated numbers it will completely
throw off the relationship. Once a number is assigned in the A column it can
never change.

Thanks,
Keith