宏循环通过下拉列表并另存为pdf [英] Macro loop through dropdown and save as pdf

问题描述

亲爱的所有

我搜索了互联网并遇到了很多部分解决方案，但是，我写的脚本给了我错误，我找不到合适的为了放置这些步骤。

我有一个包含Sheet Station Dashboard单元格D1下拉列表的Excel，该下拉列表的条目列表在Sheet"Station Mapping"中（" ; A2：A140"）

我希望excel通过该下拉列表，并将pdf保存在存储在Cell K7中的文件夹位置（与VLOOKUP公式链接，具体取决于选择的车站）。



这是我得到的：

 Sub SaveAllPDFs（）
 
 Dim cell As Range 
 Dim Dashboard As Worksheet 
 Dim counter As Long 
 Dim location As String 
 Dim PdfName As String，Title As String 
 
 ActiveWorkbook.Save 
 
 Set Dashboard = Sheets（" Station Dashboard"）
 
 For Sheets中的每个单元格（"Station Mapping"）。R ange（"A2：A140"）
 location = Range（" K7"）
 PdfName = location& Application.PathSeparator&范围（"D1"）& "  - " &安培;范围（"D7"）& " .PDF" 
如果不是IsEmpty（单元格）则
使用仪表板
 .Range（" D1"）。Value = cell.Value 
 .ExportAsFixedFormat类型：= xlTypePDF，filename：= PdfName ，Quality：= xlQualityStandard，IncludeDocProperties：= True，IgnorePrintAreas：= False，OpenAfterPublish：= True 
 End with 
 End if 
 Next cell 
 Set Dashboard = Nothing 
 End Sub 

调试器告诉我.ExportAsFixedFormat创建了一个运行时错误（1004）。保存时文档可能已打开或发生错误。

感谢您的帮助！欣赏它。

祝你好运，Joel

解决方案

 location =范围（" K7"）
 PdfName = location& Application.PathSeparator&范围（"D1"）& "  - " &安培;范围（"D7"）& " .pdf" 
 

恕我直言，显然无效路径和/或文件名的可能性很高。

如果您需要进一步的帮助，请在www.dropbox.com等在线文件托管服务器上传您的文件（可能带有匿名数据）并在此处发布下载链接。

$
可以在此处下载用于匿名选定单元格中数据的宏：

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

Andreas。

Dear all

I've searched the interweb and came across many part-solutions, however, the script I wrote gives me errors and I can't find the right order to place the steps.

I have an excel containing a dropdown in cell D1 of Sheet Station Dashboard, the list of entries for that dropdown is in the Sheet "Station Mapping"("A2:A140")

I want the excel to go through that dropdown, and save a pdf in the folder location that is stored in Cell K7 (linked with a VLOOKUP formula, depending on the station selected).

Here's what i got:

Sub SaveAllPDFs()

Dim cell As Range
Dim Dashboard As Worksheet
Dim counter As Long
Dim location As String
Dim PdfName As String, Title As String

  ActiveWorkbook.Save

    Set Dashboard = Sheets("Station Dashboard")
   
    For Each cell In Sheets("Station Mapping").Range("A2:A140")
    location = Range("K7")
    PdfName = location & Application.PathSeparator & Range("D1") & "-" & Range("D7") & ".pdf"
      If Not IsEmpty(cell) Then
         With Dashboard
            .Range("D1").Value = cell.Value
            .ExportAsFixedFormat Type:=xlTypePDF, filename:=PdfName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
         End With
      End If
   Next cell
    Set Dashboard = Nothing
End Sub

The debugger tells me that the .ExportAsFixedFormat created a runtime error (1004). Document may be open or an error occured whilst saving.

Thanks for your help! appreciate it.
Best regards, Joel

解决方案

    location = Range("K7")
    PdfName = location & Application.PathSeparator & Range("D1") & "-" & Range("D7") & ".pdf"

IMHO it is obvious that the chance for an invalid path and/or the filename is high.

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

Andreas.

这篇关于宏循环通过下拉列表并另存为pdf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！