Code: Select all
#SingleInstance, force ;Disallows more than one instance of the script to run at any given time
#include <DBA> ;This library has all the needed sqlite3 functions and error handling
xl := ComObjActive("Excel.Application") ;creates a handle to your currently active excel sheet
/*
This Macro was Written by PollaxedPotter, It is intended to be used as an automatic sqlite3 database builder.
To build a database you MUST first construct the database on an excel spreadsheet. This macro will take any active execl sheet and import it's values
into an sqlite database. At this time error handling is NOT robust (or existent) so values must be entered into the excel sheet with care.
This Macro will scale with the spread sheet, the only limit on the number of columns or rows is the spreadsheet it's self.
*/
WinActivate, ahk_class XLMAIN
lastcol := xl.Range["C2"].End(-4161).Address[0,0] ;for purpose of .END(-xxxx) right = 4161, Left = 4159, Down = 4121, Up = 4162
lastrow := xl.Range["C2"].End(-4121).Address[0,0] ;the [0,0] after Address causes the data returned to be formated LetterNumber rather than $Letter $Number
StringMid, lastRowCount, lastrow, 2,1 ;Here we strip off the Letter and are left with a variable LastrowCount which = the last row used
RowCount := lastRowCount - 2 ;We then subtract 2 to get the number of rows wich will need to be inserted into the table
xl.range("C2").select
SendInput,^+{right} ;These two lines select all Column titles
sleep, 50
countColumns := xl.selection.count ;Here we get the total number of cells selected which = total number of columns in the database
Database := xl.range("B1").Value ;Set the name of the database to be saved
Table := xl.Range("B2").Value ;Set the name of the Table to be created in the above database
dbDIR = C:\AutoHotkey\%Database%.db ;Sets the directory and name of the Database file
xl.range("C2").select
colum := Object() ;declare colum as an object/array
Loop, %countColumns% ;begins a loop that will execute once for each column being created
{
colum[A_index] := xl.ActiveCell.Value ;Stores the value of the current cell into an array. here A_Index = the current loop iteration
xl.ActiveCell.offset(0,1).select ;Shift the active cell to the right 1 cell
tempColum := colum[A_index] ;store the value of the array into a regular variable (makes the next step easier)
if (A_Index = 1)
ColumnString := Colum[A_Index]
else
ColumnString =%ColumnString%, %tempColum% ;Here we are building a string, each iteration of the loop will add to the end of the string
}
;MsgBox, %ColumnString% ;this remark included for testing
dbx := DBA.DataBaseFactory.OpenDataBase("SQLite", dbDIR) ;Here we are creating a dabase object "dbx" which will refer to the open sqlite database
qry =CREATE TABLE '%Table%'(%ColumnString%) ;Here we are creating our query and inserting the data gained from the loop above
create_table := dbx.Query(qry) ;This line passes the query into our sqlite database interface and creates the table within the database
; MsgBox, %qry% ;this remark included for testing
columns := ColumnString
xl.Range("C3").Select
loop, %RowCount% ;This loop is used to insert all of the data
{
Loop, %countColumns% ; This loop is nearly identical to the column building loop, and is used to make a similar string comprised of Row data
{
colum[A_index] := xl.ActiveCell.Value
xl.ActiveCell.offset(0,1).select
tempColum := colum[A_index]
if (A_Index = 1)
ColumnString = '%tempColum%'
else
ColumnString =%ColumnString%, '%tempColum%'
}
;MsgBox, %ColumnString% ;this remark included for testing
xl.ActiveCell.offset(1,0).select ;Here we shift the active cell down one row
WinActivate, ahk_class XLMAIN ;ensure that our excel window is active
SendInput,{home}{right}{right} ;This key sequence moves the active cell to the start of the next row
sleep,50 ;This creates a slight pause (1000 = 1 second) to allow the gui to finish adjustments.
;If the macro is not stable increasing this value may reslove the issue
qry =INSERT INTO %Table% VALUES (%ColumnString%) ;again qry is used to store the query being built
AddRow := dbx.Query(qry) ;And here we pass the query which adds the current row
;MsgBox, %qry% ;this remark included for testing
}
dbx.EndTransaction() ;Lastly we close out the sqlite database
dbx.Close()