excel 尝试使用VBA脚本创建.pdf文件,但该文件未保存在指定的位置

v2g6jxz6  于 2022-11-18  发布在  其他
关注(0)|答案(1)|浏览(89)

我目前正在编写一个子例程,允许我通过使用Excel工作表中的数据将PDF保存在文件夹中,然而,当我运行代码时,PDF在预期的文件夹中无处可寻。
下面是子程序:
(我对此非常陌生,为任何错误道歉)

Sub createfile()
        'Declare worksheet
    Dim ws As Worksheet
    Set ws = ActiveSheet

    
    ' declare location to save file as string
    Dim sStoreFile As String
    sStoreFile = "L:\My Documents\ExcelTestDocument"
    
    ' declare filename variable as string
    Dim sFilename As String
    ' declare number of files to create
    Dim iFileCount As Integer
    iFileCount = Range("A1").End(xlDown).Row - 1
    
    Dim i As Integer
    For i = 1 To iFileCount
        
        ' set the value of the file from spreadsheet
        sFilename = ws.Range("g" & i + 1).Value
        

        
        ' If extension has "pdf" then save as PDF
        If Right(sFilename, 3) = "pdf" Then
            ws.Range("A" & iFileCount + 5).Clear
            ws.Range("A" & iFileCount + 5).Value = sFilename
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFilename
            ActiveSheet.ExportAsFixedFormat Type:=xlType
        End If
        
        
   
    Next i
    
   MsgBox "All files have been completed"
    
 End Sub

我希望在子例程中提到的文件夹中找到PDF文件,但即使在子例程完成后,MSG框提示我所有文件都已创建,我仍然无法找到任何文件

ql3eal8s

ql3eal8s1#

您必须将sStoreFile添加到文件名:

' set the value of the file from spreadsheet
   sFilename = sStoreFile  & "\" & ws.Range("g" & i + 1).Value

相关问题