SQLでは
SELECT xxxxx,data,MIN(time) As times
From xxxxx
WHERE ●●●="●●●"
GROUP BY xxxxx,data;
実際に今書いているVBAが途中なのですが、
Sub createInsertSql()
Dim newbook As Workbook
Dim currentCell As Range
Dim srcSheet As Worksheet
Set srcSheet = ActiveSheet
Dim targetRange As Range
Set targetRange = srcSheet.UsedRange
Dim head As String
head = "SERECT " & srcSheet.Name & " ("
Dim first As Boolean
first = True
Dim currentColumnIndex As Integer
For currentColumnIndex = 1 To targetRange.Columns.Count
If (first) Then
first = False
Else
head = head & "xxxxx,data,MIN(time)"
End If
Set currentCell = srcSheet.Cells(1, currentColumnIndex)
head = head & currentCell.Value
Next
head = head & ") "
Set newbook = Workbooks.Add
Dim currentRowIndex As Integer
For currentRowIndex = 2 To targetRange.Rows.Count
Dim sql As String
sql = head & "values ("
first = True
For currentColumnIndex = 1 To targetRange.Columns.Count
If (first) Then
first = False
Else
sql = sql & ","
End If
Set currentCell = srcSheet.Cells(currentRowIndex, currentColumnIndex)
If IsNull(currentCell) Or Trim(currentCell.Value) = "" Then
sql = sql & "null"
ElseIf IsNumeric(currentCell.Value) Then
sql = sql & currentCell.Value
Else
sql = sql & "'" & currentCell.Value & "'"
End If
Next
sql = sql & ");"
newbook.ActiveSheet.Cells(currentRowIndex - 1, 1).Value = sql
Next
End Sub
2
1
1
1
1
コメント（3件)
>SELECT xxxxx,data,MIN(time) As times
>実際に今書いているVBAが途中なのですが、
>Dim head As String
>head = "SERECT " & srcSheet.Name & " ("
head = "SERECT " &
が
head = "SELECT " &
気になったので、コメントで。
あと、SQLを動かしたい気持ちはわかったのですが、
質問から、何を作成したいのかが？
私の理解不足でわからなかったです。
ADOを使えばEXCELファイルの中身をデータベース化して
直接SQLが発行できるようになります
ADOを一旦勉強します。
ありがとうございました！