Question

Excel VB运行时错误，路径/文件访问错误，怎么回事？

'最大行数
Const MAX_NUM_ROW = 5000
'导出文件路径所在单元格
Const PATH_OUTPUT_ROW = 3
Const PATH_OUTPUT_COL = 3
'定义列常量
Const NAME_COL = 1
Const GENDER_COL = 2
Const PHONE_COL = 3
Const EMAIL_COL = 4
'读取数据开始行数
Const START_ROW = 5
'定义数据实体类
Private Type Tmplt
NAME As String
GENDER As String
PHONE As String
EMAIL As String
End Type
'行数变量
Dim noOfTmplts As Integer
'数据实体类数组
Dim TmpltArray(MAX_NUM_ROW) As Tmplt
'点击按钮触发事件
Private Sub CommandButton1_Click()
generateSQL
End Sub
'生成SQL
Private Sub generateSQL()
makedir
initData
writeToFile
End Sub
'构建文件输出路径
Private Sub makedir()
On Error Resume Next
MkDir Sheet1.Cells(PATH_OUTPUT_ROW, PATH_OUTPUT_COL)
End Sub
'读取Excel数据，填充实体类数组
Private Sub initData()
Erase TmpltArray
noOfTmplts = 0
Dim j As Integer
'循环读取Excel数据行
For j = START_ROW To MAX_NUM_ROW
TmpltArray(noOfTmplts).NAME = Sheet1.Cells(j, NAME_COL)
TmpltArray(noOfTmplts).GENDER = Sheet1.Cells(j, GENDER_COL)
TmpltArray(noOfTmplts).PHONE = Sheet1.Cells(j, PHONE_COL)
TmpltArray(noOfTmplts).EMAIL = Sheet1.Cells(j, EMAIL_COL)
noOfTmplts = noOfTmplts + 1
Next
End Sub
'读取实体类数组,生成SQL并写入文件
Private Sub writeToFile()
Dim lvOutputPath As String
'输出文件路径
lvOutputPath = Sheet1.Cells(PATH_OUTPUT_ROW, PATH_OUTPUT_COL)
If lvOutputPath = "" Then
MsgBox "没有找到输出文件路径！"
Exit Sub
End If
fileNum = FreeFile
'打开输出文件
Open lvOutputPath For Output As fileNum

Dim lvUserSql As String
Dim nameStr As String
Dim genderStr As String
Dim phoneStr As String
Dim emailStr As String
'循环生成SQL
For j = 0 To noOfTmplts - 1
nameStr = TmpltArray(j).NAME
genderStr = TmpltArray(j).GENDER
phoneStr = TmpltArray(j).PHONE
emailStr = TmpltArray(j).EMAIL
If nameStr <> "" Then
lvUserSql = "Insert into Students(name,gender,phone,email) values('" & nameStr & "','" & genderStr & "','" & phoneStr & "','" & emailStr & "');"
Print #fileNum, lvUserSql
End If
Next
Close fileNum
MsgBox "文件生成完成！"
Exit Sub
Err_Open_File:
Close lvFileNum
If Err.Number = 76 Then
'路径未找到
MsgBox Err.Description
Exit Sub
Else
MsgBox Err.Description
Exit Sub
End If

End Sub

Answer 1

那就是你路径不对。

fileNum = FreeFile
'打开输出文件
msgbox lvoutputpath       '加上这句代码，看看弹出的路径是否正确。
Open lvOutputPath For Output As fileNum

追问

弹出的途径，没问题啊。。

追答

你单步执行，看看是到哪句代码出问题的？看看出问题的这行代码的变量都是什么值？
然后截图看看。

追问

你QQ多少？我加你吧。。这是我的 9 3 9 9 4 1 3 9 9

Answer 2

用文件系统对象吧
把下面这段插入到你的程序中

Dim f
Dim txtFile
Set f = CreateObject("scripting.Filesystemobject")
Set txtFile = f.CreateTextFile(Mypath)
txtFile.writeline "SQL******"
txtFile.Close
Set txtFile = Nothing
Set f = Nothing