vba 导出为固定格式 Excel 2007
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20982314/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export As A Fixed Format Excel 2007
提问by
I have been assigned the task of developing a excel document that whole office will use. The user will click a button and the macro will export the file as a PDF to a shared folder. I wrote this code and tested this code using excel 2010. People that have excel 2007 where getting an error message saying "Run Time Error 1004 Document not saved. This document may be open, or an error may have been encountered when saving." I looked into the problem a little bit and found that excel 2007 needed an add-in update, so I installed it on their computers. I also checked to see if they have adobe on their computers and they do. They are still having the problem and I am unsure of what to do. Any help would be greatly appreciated!
我的任务是开发一个整个办公室都会使用的 excel 文档。用户将单击一个按钮，宏会将文件作为 PDF 导出到共享文件夹。我编写了这段代码并使用 excel 2010 测试了这段代码。拥有 excel 2007 的人收到一条错误消息，指出“运行时错误 1004 文档未保存。此文档可能已打开，或者保存时可能遇到错误。” 我稍微调查了一下问题，发现excel 2007需要一个插件更新，所以我在他们的电脑上安装了它。我还检查了他们的计算机上是否有 adobe，他们确实有。他们仍然有问题，我不确定该怎么做。任何帮助将不胜感激！
Here is my code
这是我的代码
' Define all variables
Dim strFileName As String
Dim folder As String
Dim member As Integer
Dim member_count As Integer
Dim member_name As String
Dim show As Variant
Dim MyTime As String
'Save as new file
Worksheets("Input data").Visible = True
folder = Sheets("Input data").Range("location").Value
MyTime = Time
Sheets("Input data").Select
Range("G2").Value = MyTime
strFileName = folder & "Material Request - " & Sheets("Input data").Range("name").Value & "_" & Sheets("Input data").Range("date").Value & " " & Sheets("Input data").Range("time").Value & ".pdf"
Sheets("Material Request").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName 'OpenAfterPublish:=True`
采纳答案by PatricK
You should start with changing the code to remove .Select& .ActiveSheetinstances.
您应该从更改代码以删除.Select&.ActiveSheet实例开始。
Dim oWS as Worksheet
Set oWS = ThisWorkbook.Worksheets("Input data")
' Worksheets("Input data").Visible = True
folder = oWS.Range("location").Value
If Right(folder,1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator
MyTime = Time
' Sheets("Input data").Select
oWS.Range("G2").Value = MyTime
strFileName = folder & "Material Request - " & oWS.Range("name").Value & "_" & oWS.Range("date").Value & " " & oWS.Range("time").Value & ".pdf"
Debug.Print "strFileName: " & strFileName
'Sheets("Material Request").Select
oWS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName 'OpenAfterPublish:=True`
Set oWS = Nothing
Refer to this MSDN Worksheet.ExportAsFixedFormat Method, you may need fill in more parameters depending on properties of the Worksheet "Input Data".
参考这个MSDN Worksheet.ExportAsFixedFormat Method，您可能需要根据工作表“输入数据”的属性填写更多参数。
I have added some checks and refer to Immediate window to check value of strFileNamein 2007.
我添加了一些检查并参考立即窗口来检查strFileName2007 年的值。
回答by user1125879
I had a similiar problem (Error 1004 when attempting export). After an hour of pulling my hair out, here was the source of my problem.
我遇到了类似的问题（尝试导出时出现错误 1004）。拔出我的头发一个小时后，这是我问题的根源。
I was passing a cell value as part of generating the filename. I was doing this in the format of
我正在传递一个单元格值作为生成文件名的一部分。我这样做的格式
fileName:= ActiveWorkbook.Path & "\" & CStr(Workbooks.Cells(i,j).Value) & ".pdf"
The text in the cell itself was formatted to be in two rows (i.e. "top row text" + (Alt+K) + "bottom row text"). While the string looks normal in Debug.print, MsgBox, or value previews, I am thinking that there is a hidden character which encodes the new line for the cell. I believe this hidden character causes the error when passed as part of the fileName argument. I'm guessing Excel doesn't pick it up but the OS's file name system does.
单元格本身的文本被格式化为两行（即“顶行文本”+（Alt+ K）+“底行文本”）。虽然字符串在Debug.print、MsgBox或 值预览中看起来正常，但我认为有一个隐藏字符对单元格的新行进行编码。我相信这个隐藏字符在作为 fileName 参数的一部分传递时会导致错误。我猜 Excel 不会选择它，但操作系统的文件名系统会。
In any case, this fixed the issue for me.
无论如何，这为我解决了问题。
