之前在做数据开发时候遇到的问题数据表格批量生成建表的宏出问题了，此时只好硬着头皮写vba代码,此时您想我不会vba也不知道怎么使用但是sql语句建表格式总还是知道的那么此刻开始我们的提示词开发代码时刻
先进行具体提示词开发>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>您打开了kimi然后问道
表格Vba如何在sheet1输入表明和字段名和字段类型，字段长度，字段是否为空，是否主键，中文注释，自动生成建表并且可以生成不同的表并且输出到d盘文件txt上
Sub GenerateSQLAndOutputToDDrive()
Dim wsInput As Worksheet
Dim sql As String
Dim i As Long, j As Long
Dim tableName As String
Dim fieldName As String
Dim fieldType As String
Dim fieldLength As String
Dim isNullable As String
Dim isPrimaryKey As String
Dim fieldComment As String
Dim outputFilePath As String
Dim dict As Object
Dim lastRow As Long
' 创建一个字典来存储已经处理过的表名
Set dict = CreateObject("Scripting.Dictionary")
' 设置输入工作表
Set wsInput = ThisWorkbook.Sheets("Sheet1")
' 设置输出文件路径
outputFilePath = "D:\CreateTableSQL.txt"
' 打开文件准备写入
Open outputFilePath For Output As #1
' 遍历每一行数据
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
tableName = wsInput.Cells(i, "A").Value
' 检查是否已经处理过这个表名
If Not dict.Exists(tableName) Then
sql = "CREATE TABLE " & tableName & " (" & vbCrLf
' 初始化一个标志来标记是否是第一个字段
Dim isFirstField As Boolean
isFirstField = True
' 构建字段行
For j = 2 To lastRow
If wsInput.Cells(j, "A").Value = tableName Then
fieldName = wsInput.Cells(j, "B").Value
fieldType = wsInput.Cells(j, "C").Value
fieldLength = wsInput.Cells(j, "D").Value
isNullable = IIf(wsInput.Cells(j, "E").Value = "YES", "NOT NULL", "NULL")
isPrimaryKey = IIf(wsInput.Cells(j, "F").Value = "YES", "PRIMARY KEY", "")
fieldComment = wsInput.Cells(j, "G").Value
If isFirstField Then
isFirstField = False
Else
sql = sql & ", " & vbCrLf
End If
sql = sql & " " & fieldName & " " & fieldType
If fieldLength <> "" Then
sql = sql & "(" & fieldLength & ")"
End If
sql = sql & " " & isNullable & " " & isPrimaryKey
If fieldComment <> "" Then
sql = sql & " COMMENT '" & fieldComment & "'"
End If
End If
Next j
' 移除最后一个逗号
If Len(sql) > 2 Then
sql = Left(sql, Len(sql) - 2)
End If
sql = sql & vbCrLf & ");" & vbCrLf
' 输出到文本文件
Print #1, sql
' 将表名添加到字典中
dict.Add tableName, Nothing
End If
Next i
' 关闭文件
Close #1
MsgBox "SQL语句已生成并输出到：" & outputFilePath
End Sub
您发现生成出来代码中文注释乱码于是问了kimi
1. **确保VBA编辑器使用正确的字体**：可以通过VBA编辑器的“工具”->“选项”->“编辑器格式”来更改字体，选择支持中文的字体，如宋体或微软雅黑。
可是新的问题是末尾还被多吃了注释和结尾
于是你拿着代码问
Sub GenerateSQLAndOutputToDDrive()
Dim wsInput As Worksheet
Dim sql As String
Dim i As Long, j As Long
Dim tableName As String
Dim fieldName As String
Dim fieldType As String
Dim fieldLength As String
Dim isNullable As String
Dim isPrimaryKey As String
Dim fieldComment As String
Dim outputFilePath As String
Dim dict As Object
Dim lastRow As Long
' 创建一个字典来存储已经处理过的表名
Set dict = CreateObject("Scripting.Dictionary")
' 设置输入工作表
Set wsInput = ThisWorkbook.Sheets("Sheet1")
' 设置输出文件路径
outputFilePath = "D:\CreateTableSQL.txt"
' 打开文件准备写入
Open outputFilePath For Output As #1
' 遍历每一行数据
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
tableName = wsInput.Cells(i, "A").Value
' 检查是否已经处理过这个表名
If Not dict.Exists(tableName) Then
sql = "CREATE TABLE " & tableName & " (" & vbCrLf
' 初始化一个标志来标记是否是第一个字段
Dim isFirstField As Boolean
isFirstField = True
' 构建字段行
For j = 2 To lastRow
If wsInput.Cells(j, "A").Value = tableName Then
fieldName = wsInput.Cells(j, "B").Value
fieldType = wsInput.Cells(j, "C").Value
fieldLength = wsInput.Cells(j, "D").Value
isNullable = IIf(wsInput.Cells(j, "E").Value = "YES", "NOT NULL", "NULL")
isPrimaryKey = IIf(wsInput.Cells(j, "F").Value = "YES", "PRIMARY KEY", "")
fieldComment = wsInput.Cells(j, "G").Value
If isFirstField Then
isFirstField = False
Else
sql = sql & ", " & vbCrLf
End If
sql = sql & " " & fieldName & " " & fieldType
If fieldLength <> "" Then
sql = sql & "(" & fieldLength & ")"
End If
sql = sql & " " & isNullable & " " & isPrimaryKey
If fieldComment <> "" Then
sql = sql & " COMMENT '" & fieldComment & "'"
End If
End If
Next j
' 移除最后一个逗号
If Len(sql) > 2 Then
sql = Left(sql, Len(sql) - 2)
End If
sql = sql & vbCrLf & ");" & vbCrLf
' 输出到文本文件
Print #1, sql
' 将表名添加到字典中
dict.Add tableName, Nothing
End If
Next i
' 关闭文件
Close #1
MsgBox "SQL语句已生成并输出到：" & outputFilePath
End Sub这段代码优化下最后一个逗号需要消失并且不影响最后一个字段注释这段 VBA 代码的目的是从一个 Excel 工作表中读取数据，然后生成 SQL 创建表的语句，并将这些语句输出到一个文本文件中。代码中存在一个小问题，即在生成 SQL 语句时，最后一个字段后面可能会多出一个逗号。为了解决这个问题，我们可以在循环的最后检查是否需要添加逗号，而不是在每次迭代时都添加。
以下是优化后的代码：
```vba
Sub GenerateSQLAndOutputToDDrive()
Dim wsInput As Worksheet
Dim sql As String
Dim i As Long, j As Long
Dim tableName As String
Dim fieldName As String
Dim fieldType As String
Dim fieldLength As String
Dim isNullable As String
Dim isPrimaryKey As String
Dim fieldComment As String
Dim outputFilePath As String
Dim dict As Object
Dim lastRow As Long
Dim fieldCount As Long
' 创建一个字典来存储已经处理过的表名
Set dict = CreateObject("Scripting.Dictionary")
' 设置输入工作表
Set wsInput = ThisWorkbook.Sheets("Sheet1")
' 设置输出文件路径
outputFilePath = "D:\CreateTableSQL.txt"
' 打开文件准备写入
Open outputFilePath For Output As #1
' 遍历每一行数据
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
tableName = wsInput.Cells(i, "A").Value
' 检查是否已经处理过这个表名
If Not dict.Exists(tableName) Then
sql = "CREATE TABLE " & tableName & " (" & vbCrLf
fieldCount = 0 ' 初始化字段计数器
' 构建字段行
For j = 2 To lastRow
If wsInput.Cells(j, "A").Value = tableName Then
fieldName = wsInput.Cells(j, "B").Value
fieldType = wsInput.Cells(j, "C").Value
fieldLength = wsInput.Cells(j, "D").Value
isNullable = IIf(wsInput.Cells(j, "E").Value = "YES", "NOT NULL", "NULL")
isPrimaryKey = IIf(wsInput.Cells(j, "F").Value = "YES", "PRIMARY KEY", "")
fieldComment = wsInput.Cells(j, "G").Value
fieldCount = fieldCount + 1 ' 增加字段计数器
If fieldCount > 1 Then
sql = sql & ", " & vbCrLf
End If
sql = sql & " " & fieldName & " " & fieldType
If fieldLength <> "" Then
sql = sql & "(" & fieldLength & ")"
End If
sql = sql & " " & isNullable & " " & isPrimaryKey
If fieldComment <> "" Then
sql = sql & " COMMENT '" & fieldComment & "'"
End If
End If
Next j
sql = sql & vbCrLf & ");" & vbCrLf
' 输出到文本文件
Print #1, sql
' 将表名添加到字典中
dict.Add tableName, Nothing
End If
Next i
' 关闭文件
Close #1
MsgBox "SQL语句已生成并输出到：" & outputFilePath
End Sub
```
在这个优化后的代码中，我添加了一个名为 `fieldCount` 的变量来跟踪每个表中字段的数量。在构建 SQL 语句时，只有在字段计数器大于 1 时（即不是第一个字段），才会添加逗号和换行符。这样可以确保最后一个字段后面不会有多余的逗号。
优化代码后您在d盘拿到结果了
成功输出批量建表的需求宏，你开心的懂了》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》
小白通过和机器人不断提示沟通于是自己也会解决一下一些问题，如果您 还不会提示词可以围绕what，result,how来不断 获取信息纠正信息获取您想要的结果。拥抱机器时代来临。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
