Snasui Excel Forum

Code: Select all

            " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Data$A2:CR100000]"

Code: Select all

Sub SaveData()

    Dim sFile As String, sh As Worksheet
    Dim sCnstr As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, shtName As String
    Dim arr() As Variant, i As Integer, j As Integer
    
    sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
    shtName = "[Data$]"
    strSql = "INSERT INTO [sFile$]" & _
            " SELECT * FROM [Excel 12.0 Macro;HDR=Yes;" & _
            " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Data$A2:CR100000]" & _
           " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Data$HN,Data$Name,Data$DOB,Data$Payer] value [Sheets(1).(""InputHN"").value,Sheets(1).(""InputName"").value,Sheets(1).(""C12"").value,Sheets(1).(""InputPayer"").value]"

    sCnstr.CursorLocation = adUseClient
    sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=" & sFile & ";" _
        & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    rs.Open sql, sCnstr
    
    Set sCnstr = Nothing
    Set rs = Nothing
End Sub

Code: Select all

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Code: Select all

'----other Code-----
    strSql = "INSERT INTO [sFile$]" & _
            " SELECT * FROM [Excel 12.0 Macro;HDR=Yes;" & _
            " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Data$A1:CR2]" & _
            " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Data$HN,Data$Name,Data$DOB,Data$Payer] values [Sheets(1).(""InputHN"").value,Sheets(1).(""InputName"").value,Sheets(1).(""C12"").value,Sheets(1).(""InputPayer"").value]"

    sCnstr.CursorLocation = adUseClient
'----other Code-----

Code: Select all

 " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Input$A6:H22]"

Code: Select all

 " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Input$A26:CR27]"

Code: Select all

Sub SaveData()

    Dim sFile As String, sh As Worksheet
    Dim sCnstr As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, shtName As String
    Dim arr() As Variant, i As Integer, j As Integer
    
     sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
    shtName = "[Data$]"
    strSql = "INSERT INTO [sFile$]" & _
            " SELECT * FROM [Excel 12.0 Macro;HDR=Yes;" & _
            " Database=" & ThisWorkbook.FullName & ";Readonly=False].[Input$A26:CR27]"


    sCnstr.CursorLocation = adUseClient
    sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=" & sFile & ";" _
        & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    rs.Open sql, sCnstr
    
    Set sCnstr = Nothing
    Set rs = Nothing
End Sub

Code: Select all

Sub Savetofile_Click()

Dim r As Integer
Dim i As Integer

i = 1

r = Sheets("input").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("input").Cells(r, 2).Value = Day(Date)
Sheets("input").Cells(r, 3).Value = Month(Date)
Sheets("input").Cells(r, 4).Value = Year(Date) - 2000
Sheets("input").Cells(r, 5).Value = i
i = i + 1

Sheets("input").Cells(r, 2).NumberFormat = "00"
Sheets("input").Cells(r, 3).NumberFormat = "00"
Sheets("input").Cells(r, 5).NumberFormat = "0000"

Refno = Sheets("input").Cells(r, 4).Text & "-" & Sheets("input").Cells(r, 3).Text & "-" & Sheets("input").Cells(r, 2).Text & "-" & Sheets("input").Cells(r, 5).Text
Sheets("input").Cells(r, 1).Value = Refno

Sheets("Input").Range("InputRefNo") = Refno

Sheets("input").Cells(r, 6).Value = Sheets("Input").Range("InputName").Value
Sheets("input").Cells(r, 7).Value = Sheets("Input").Range("InputHN").Value
Sheets("input").Cells(r, 8).Value = Sheets("Input").Range("C12").Value
Sheets("input").Cells(r, 9).Value = Sheets("Input").Range("InputPayer").Value


Sheets("Input").Range("InputEstimateDateTime").Value = Now
Sheets("input").Cells(r, 95).Value = Sheets("Input").Range("InputEstimateDateTime").Value

    
    
End Sub
[Code]

Code: Select all

Sub Savetofile_Click()

Dim r As Integer
Dim i As Integer

i = 1

r = Sheets("input").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("input").Cells(r, 2).Value = Day(Date)
Sheets("input").Cells(r, 3).Value = Month(Date)
Sheets("input").Cells(r, 4).Value = Year(Date) - 2000
Sheets("input").Cells(r, 5).Value = i
i = i + 1

Sheets("input").Cells(r, 2).NumberFormat = "00"
Sheets("input").Cells(r, 3).NumberFormat = "00"
Sheets("input").Cells(r, 5).NumberFormat = "0000"

Refno = Sheets("input").Cells(r, 4).Text & "-" & Sheets("input").Cells(r, 3).Text & "-" & Sheets("input").Cells(r, 2).Text & "-" & Sheets("input").Cells(r, 5).Text
Sheets("input").Cells(r, 1).Value = Refno

Sheets("Input").Range("InputRefNo") = Refno

Sheets("input").Cells(r, 6).Value = Sheets("Input").Range("InputName").Value
Sheets("input").Cells(r, 7).Value = Sheets("Input").Range("InputHN").Value
Sheets("input").Cells(r, 8).Value = Sheets("Input").Range("C12").Value
Sheets("input").Cells(r, 9).Value = Sheets("Input").Range("InputPayer").Value


Sheets("Input").Range("InputEstimateDateTime").Value = Now
Sheets("input").Cells(r, 95).Value = Sheets("Input").Range("InputEstimateDateTime").Value
   
    
End Sub

Code: Select all

Sub Savetofile_Click()
 Dim sFile As String, sh As Worksheet
    Dim sCnstr As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, shtName As String
    Dim arr() As Variant, i As Integer, j As Integer
    sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
    shtName = "[Data$]"
    sql = "select * from " & shtName & " ' Order By 'RefNo' DESC LIMIT 1"
    sCnstr.CursorLocation = adUseClient
    sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=" & sFile & ";" _
        & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    rs.Open sql, sCnstr
    myReader = sql.ExecuteReader();
   while (myReader.Read())
    {
        Worksheets("input").Range("L22").Value = (myReader["RefNo"].ToString());
    }

    Set sCnstr = Nothing
    Set rs = Nothing 

Code: Select all

'Other code...
sql = "select top 1 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "Data Source=" & sFile & ";" _
    & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
MsgBox rs.Fields(0).Value
'    myReader = sql.ExecuteReader();
'   While (myReader.Read())
'    {
'        Worksheets("input").Range("L22").Value = (myReader["RefNo"].ToString());
'    }

Set sCnstr = Nothing
'Other code...

Code: Select all

'Other code...
r = Sheets("input").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("input").Cells(r, 2).Value = Day(Date)
Sheets("input").Cells(r, 3).Value = Month(Date)
Sheets("input").Cells(r, 4).Value = Year(Date) - 2000
 Sheets("input").Cells(r, 5).Value = rs.Fields(4).Value + 1
 
 Sheets("input").Cells(r, 2).NumberFormat = "00"
Sheets("input").Cells(r, 3).NumberFormat = "00"
Sheets("input").Cells(r, 5).NumberFormat = "0000"

Refno = Sheets("input").Cells(r, 4).Text & "-" & Sheets("input").Cells(r, 3).Text & "-" & Sheets("input").Cells(r, 2).Text & "-" & Sheets("input").Cells(r, 5).Text
Sheets("input").Cells(r, 1).Value = Refno

Sheets("Input").Range("InputRefNo") = Refno
'Other code...

Code: Select all

'Other code...
With Sheets("input")
    r = .Cells(Rows.Count, 1).End(xlUp).Row + 1
  
    .Cells(r, 2).Value = Day(Date)
    .Cells(r, 3).Value = Month(Date)
    .Cells(r, 4).Value = Year(Date) - 2000
    .Cells(r, 2).NumberFormat = "00"
    .Cells(r, 3).NumberFormat = "00"
    .Cells(r, 5).NumberFormat = "0000"
    
    refno = .Cells(r, 4).Text & "-" & .Cells(r, 3).Text & "-" & .Cells(r, 2).Text
    
    If VBA.Left(rs.Fields(0).Value, 8) = refno Then
        .Cells(r, 5).Value = rs.Fields(4).Value + 1
    Else
        .Cells(r, 5).Value = 1
    End If
    refno = refno & "-" & VBA.Format(.Cells(r, 5).Value, "0000")
    .Cells(r, 1).Value = refno
    .Range("InputRefNo") = refno
End With
'Other code

Code: Select all

Sub DataReview(ByVal GoToRow As Integer)

    Dim sFile As String, sh As Worksheet
    Dim sCnstr As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, shtName As String
    Dim arr() As Variant, i As Integer, j As Integer, k As Integer
    Dim strS As String
    
    sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
    shtName = "[Data$]"
        sql = "select * from " & shtName & " Order By [RefNo] DESC"
        sCnstr.CursorLocation = adUseClient
        sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "Data Source=" & sFile & ";" _
    & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        rs.Open sql, sCnstr
    
With Sheets("input")
    .Range("InputRefNo") = rs.Fields(GoToRow, 0).Value
    .Range("InputName") = rs.Fields(GoToRow, 5).Value
    .Range("InputHN").Value = rs.Fields(GoToRow, 6).Value
    .Range("C12").Value = rs.Fields(GoToRow, 7).Value
     .Range("InputPayer").Value = rs.Fields(GoToRow, 8).Value
     .Range("InputEstimateDateTime").Value = rs.Fields(GoToRow, 94).Value
End With
        Set sCnstr = Nothing
End Sub

Sub PreviousData()

Dim GoToRow As Integer

    Dim sFile As String, sh As Worksheet
    Dim sCnstr As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, shtName As String
    Dim arr() As Variant, i As Integer, j As Integer, k As Integer
    Dim strS As String
    
    sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
    shtName = "[Data$]"
        sql = "select * from " & shtName & " Order By [RefNo] DESC"
        sCnstr.CursorLocation = adUseClient
        sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "Data Source=" & sFile & ";" _
    & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        rs.Open sql, sCnstr

If Worksheets("Input").Range("InputRow") = "" Then
    GoToRow = rs.Fields.Range("A1000000").End(xlUp).Row
Else
    GoToRow = rs.Fields.Range("InputRow").Value - 1

End If

Worksheets("Input").Range("InputRow").Value = GoToRow
DataReview (GoToRow)

End Sub