在Excel中，使用VBA，如何采用“路径+文件名+扩展名”并更改扩展名？ [英] In Excel, using VBA, how do I take the "path+filename+extension" and change the extension?

问题描述

有一个动态生成Excel文件和csv的程序。 excel文件有VBA代码加载csv数据加载，我想通过使csv文件具有相同的文件名，但只是不同的扩展名动态调用该csv。所以，基于我的理解，如果xls文件在这里：

  C：\directory\filename.xls 
  

此VBA代码：

 code>函数GetFullName（）As String 
 
 GetFullName = ThisWorkbook.FullName 
 
结束函数
  

将导致

  GetFullName（）=C：\directory \filename.xls
  

所以，如果这是正确的（源代码），如何用.csv替换.xls，然后将该值插入文件调用。或者，例如，要保持简单，该文件具有使用此代码打印PDF的VBA;这将需要我使用GetFullName，并将.xls更改为.pdf：

  Sub PrintPDF（）
 
 ActiveSheet.ExportAsFixedFormat类型：= xlTypePDF，文件名：= _ 
C：\directory\filename.pdf，质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True ，IgnorePrintAreas：= False，OpenAfterPublish：= False _ 
 
 End Sub 
  

希望问题很清楚，如果有一个更好的解决方案概念，我是开放的。如果你有问题，只要让我知道。

解决方案

替换可能正是你想要的：

 函数GetFullNameCSV（）As String 
 
 GetFullNameCSV =替换（ThisWorkbook.FullName，.xls，。csv）
 
结束函数
  

您可以通过添加您想要的扩展名来扩展此功能：

  sFileName = GetNewExt（pdf）
 
函数GetNewExt（Ext As String）As String 
 
 GetNewExt =替换（ThisWorkbook.FullName，.xls，。& Ext）
 
结束函数
  

Have a program that's dynamically generating an Excel file and a csv. The excel file has VBA code that loads the csv data "on load" and I want to dynamically call that csv by having the csv file have the same filename, but just the different extension. So, based on my understanding this if the xls file was here:

C:\directory\filename.xls

This VBA code:

Function GetFullName() As String

 GetFullName = ThisWorkbook.FullName 

End Function

Would result in

GetFullName() = "C:\directory\filename.xls"

So, if that's correct (source of code), how do I replace ".xls" with ".csv" and then insert that value into a file call. Or for example, to keep it simple, the file has VBA that prints a PDF using this code; which would require me to use the GetFullName, and change ".xls" to ".pdf":

Sub PrintPDF()

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\directory\filename.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False _

End Sub

Hope the problem is clear, if there's a better solution concept, I'm open to it. If you've got questions, just let me know.

解决方案

Replace could be exactly what you want:

Function GetFullNameCSV() As String

 GetFullNameCSV = Replace(ThisWorkbook.FullName, ".xls",".csv")  

End Function

You can extend this by including the extension you want like so:

sFileName = GetNewExt("pdf")

Function GetNewExt(Ext As String) As String

 GetNewExt = Replace(ThisWorkbook.FullName, ".xls","." & Ext)  

End Function

这篇关于在Excel中，使用VBA，如何采用“路径+文件名+扩展名”并更改扩展名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！