本帖最后由 daxindianqi 于 2016-7-1 16:11 编辑
各位老师:
这是一段EXCEL VBA 代码,执行后将工作簿按照不同单元格内容组合成的文件名保存到“z:\报价文件”文件夹下,现在我要求文件保存到工作簿本身所在目录下的“报价文件”文件夹内,代码该怎么样写??? - Dim myfilepath As String
- Private Sub CommandButton1_Click()
- Application.DisplayAlerts = False
- If Range("a1") = "" Or Range("f3") = "" Or Range("h3") = "" Or Range("b7") = "" Or Range("b6") = "" Then
- Unload UserForm4
- MsgBox "请将公司、项目、业务员、报价员信息填写完全再保存 !"
- Else
- Dim fso As Scripting.FileSystemObject
- Application.ScreenUpdating = False
- lr = 30
- lr1 = 50
- With ThisWorkbook
- Workbooks.Add (1)
- .Sheets(2).Range("A:I").Copy
- ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
- ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
- ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
- ActiveWorkbook.Sheets.Add
- .Sheets(1).Range("A:H").Copy
- ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
- ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
- ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
- End With
- myFolder = "z:\报价文件"
- Set fso = New Scripting.FileSystemObject
- If fso.FolderExists(myFolder) Then
- Call 导出数据
- Else
- fso.CreateFolder myFolder
- Call 导出数据
- End If
- Set fso = Nothing
- Application.ScreenUpdating = True
- MsgBox "数据已被导出, " & myfilepath '保存在D:\报价文件\...", 48, "导出提示"
- Unload UserForm4
- End If
- End Sub
- Sub 导出数据()
- On Error Resume Next
- Application.DisplayAlerts = False
- Call getfilepath
- ActiveWorkbook.SaveAs myfilepath
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- ActiveWorkbook.Open
- End Sub
- Private Sub CommandButton2_Click()
- On Error Resume Next
- Unload UserForm4
- End Sub
- Private Sub getfilepath()
- On Error Resume Next
- Dim i%
- i = 1
- Do
- myfilepath = "z:\报价文件\ MSGM(JS)" & "_" & Range("B2") & "_TO " & Range("B6") & "." & Range("B7") & "." & Range("G6") & " ( " & "报价" & " ) " & "_ FROM." & Range("F3") & " ( " & Range("H3") & " ) _" & Range("D2") & "_V" & i & ".00" & ".xlsx"
- i = i + 1
- Loop Until Not IsFileExists(myfilepath)
- End Sub
- Private Function IsFileExists(ByVal strFileName As String) As Boolean
- On Error Resume Next
- If Dir(strFileName, 16) <> Empty Then
- IsFileExists = True
- Else
- IsFileExists = False
- End If
- End Function
复制代码
本帖最后由 zjdh 于 2016-7-1 16:11 编辑
myFolder = "z:\报价文件"
改成
myFolder = ThisWorkbook.Path & "\报价文件"
myfilepath = "z:\报价文件\ MSGM(JS)" ......
改成
myfilepath = ThisWorkbook.Path & "\报价文件\ MSGM(JS)" .........
MsgBox "数据已被导出, " & myfilepath '保存在D:\报价文件\...", 48, "导出提示"
改成
MsgBox "数据已被导出, " & myfilepath & "保存在" & ThisWorkbook.Path & "\报价文件\...", 48, "导出提示"
|