Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoIncrementing a Column like a database in Excel 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoIncrementing a Column like a database in Excel 2007
On Thu, 24 Jul 2008 15:21:01 -0700, keith
wrote: 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 -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoIncrementing a Column like a database in Excel 2007
On Jul 24, 6:21*pm, keith wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoIncrementing a Column like a database in Excel 2007
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 "Dick Kusleika" wrote: On Thu, 24 Jul 2008 15:21:01 -0700, keith wrote: 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 -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoIncrementing a Column like a database in Excel 2007
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 " wrote: On Jul 24, 6:21 pm, keith wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I modify a database in Excel 2007? | Excel Worksheet Functions | |||
Database argument in Excel 2007 | Excel Worksheet Functions | |||
Autoincrementing dates | Excel Discussion (Misc queries) | |||
Autoincrementing dates | Excel Worksheet Functions | |||
autoincrementing dde link | Excel Discussion (Misc queries) |