stevesohcot

Public Function ImportData(sheet As String)
     
    Dim i As Long
    Dim strSQL As String
    Dim totalRows As Long
   
    totalRows = HowManyRows(sheet)
    strInsertHeader = GetInsertHeader()
    

stevesohcot

Public Function HowManyRows(sheetName As String) As Long

    'Find out how many row there are
    ' Assumes the first column does NOT have a blank in it
 
    Sheets(sheetName).Select
    Range("A1").Select

    Selection.End(xlDown).Select
   

stevesohcot

Public Sub autoImport()

    ' First delete existing data
    Dim strSQL As String
    strSQL = "DELETE FROM myTableName" ' Could potentially TRUNCATE instead
    Call RunSQL(strDbConn, strSQL)
    
    
    ' Import the new data
    Call ImportData("tabWithData") ' Be sure to pass in the name of your worksheet, or get it dynamically

stevesohcot

Public Function ImportData(sheet As String)
   
    Dim i As Integer
   
    Dim strSQL As String
    Dim totalRows As Long
   
    totalRows = HowManyRows(sheet)
    strInsertHeader = GetInsertHeader()
        

stevesohcot

Function Nz(value As String) As Double

    If IsNull(value) Or (value = "") Then
        Nz = 0
    Else
        Nz = value
    End If

End Function

stevesohcot

Public Function ReplaceSingleQuote(str As String) As String
 
    If Len(Trim(str)) > 0 Then
        ReplaceSingleQuote = Replace(str, "'", "''")
    Else
        ReplaceSingleQuote = str
    End If
 
End Function

stevesohcot

Public Function RemoveLastCharacterIfComma(str As String) As String
 
    str = Trim(str)
   
    If Right(str, 1) = "," Then
        RemoveLastCharacterIfComma = Left(str, Len(str) - 1)
    Else
        RemoveLastCharacterIfComma = str
    End If
    

stevesohcot

Public Function getSQLForSingleRow(sheet As String, rowNumber As Long) As String
  
    ' Be sure to update any formatting; ex date or number
    fieldWithText = ReplaceSingleQuote(Worksheets(sheet).Range("A" & rowNumber).FormulaR1C1)
    fieldWithDate = Format(Worksheets(sheet).Range("B" & rowNumber).FormulaR1C1, "mm/dd/yyyy")
    fieldWithNumbers = CDbl(Nz(Worksheets(sheet).Range("C" & rowNumber).FormulaR1C1))
  
    
    Dim strSQL As String
 

stevesohcot

Public Function HowManyRows(sheetName As String) As Integer
 
    'Find out how many row there are
 
    Dim i As Integer
    Sheets(sheetName).Select
    Range("A1").Select
   
    Do
        ActiveCell.Offset(1, 0).Select

stevesohcot

Public Function GetInsertHeader() As String

    Dim strHeader  As String
    strHeader = ""
    strHeader = strHeader & " INSERT INTO myTableName ("
    strHeader = strHeader & " [field_one]"
    strHeader = strHeader & " ,[field_two]"
    strHeader = strHeader & " ,[field_three]"
    strHeader = strHeader & " )"