适用VBS在当前目录到文件 [英] apply VBS to file in current directory

问题描述

我有一些VBS code，我用它来申请的Excel格式，以A S preadsheet：

 设置xlObj =的CreateObject（Excel.Application）
设置xlFile = xlObj.WorkBooks.Open（C：\\ Documents和Settings \\用户\\ forms.xlsx）
xlObj.Application.DisplayAlerts =假
对于每个工作表在xlFile.Worksheets
  dPriorWorkday = xlObj.Application.WorksheetFunction.WorkDay（现在，-1）
  Worksheet.Name =形式获得＆放大器;年（dPriorWorkday）及 - 与＆amp;右（0＆放大器;月份（dPriorWorkday），2）及 - 与＆amp;右（0和天（dPriorWorkday），2）
随着Worksheet.Cells.Font
   。名称=宋体
   .Size = 8
结束与
下一个
xlFile.Close真
xlObj.Quit
 

我用一个.bat文件上运行一个工作表的.xlsx这个VBS，所有的3个文件在同一目录之中。

我所试图做的是改变VBS的格式应用于一个.xlsx文件是在同一目录中的.bat和.vbs文件，这样我就可以删除的直接文件路径的 C：\\ Documents和Settings \\用户\\的

我曾尝试：

  Workbooks.Open名：= ThisWorkbook.Path＆安培; \\ forms.xlsx
 

但它不工作，我想因为这是从名为.vbs工作簿本身调用。

有没有类似的东西，我可以使用该.vbs适用于相同的目录中该.vbs文件，而无需使用完整的文件路径？

解决方案

  

我所试图做的是改变VBS的格式应用于一个.xlsx文件是在同一目录中的.bat和.vbs文件，这样我就可以删除的直接文件路径C：\\的Documents and Settings \\用户名\\

如果所有文件都在同一个目录，然后使用该

 设置的WshShell =的CreateObject（WScript.Shell）
strCurDir = WshShell.CurrentDirectory设置xlObj =的CreateObject（Excel.Application）
设置xlFile = xlObj.Workbooks.Open（strCurDir＆安培;\\ forms.xlsx）
 

更多关于 CurrentDirectoy 的这里

修改

Ekkehard.Horner的答复是更容易，并且可以处理这种情况时，VBA从不同的目录中调用。在这种情况下， CurrentDirectoy 显然不会产生预期的路径。

I have some VBS code that I use to apply Excel formatting to a spreadsheet:

Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("C:\Documents and Settings\user\forms.xlsx")
xlObj.Application.DisplayAlerts = False
For Each Worksheet In xlFile.Worksheets
  dPriorWorkday = xlObj.Application.WorksheetFunction.WorkDay(Now, -1)
  Worksheet.Name = "Forms received " & Year(dPriorWorkday) & "-" & Right("0" & Month(dPriorWorkday),2) & "-" & Right("0" & Day(dPriorWorkday),2)
With Worksheet.Cells.Font
   .Name = "Arial"
   .Size = 8
End With
Next 
xlFile.Close True
xlObj.Quit

I use a .bat file to run this VBS on a .xlsx worksheet, with all 3 files being in the same directory.

What I am trying to do is change the VBS to apply the formatting to a .xlsx file that is in the same directory as the .bat and the .vbs file, so I can remove the direct file path "C:\Documents and Settings\user\"

I have tried:

Workbooks.Open Filename:=ThisWorkbook.Path & "\forms.xlsx"

but it does not work, I assume because that is for .vbs called from the workbook itself.

is there something similar I can use to apply the .vbs to the file in the same directory as the .vbs, without using the full file path?

解决方案

What I am trying to do is change the VBS to apply the formatting to a .xlsx file that is in the same directory as the .bat and the .vbs file, so I can remove the direct file path "C:\Documents and Settings\user\"

If all the files are in the same directory then use this

Set WshShell = CreateObject("WScript.Shell")
strCurDir = WshShell.CurrentDirectory

Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.Workbooks.Open(strCurDir & "\forms.xlsx")

More about CurrentDirectoy HERE

EDIT:

Ekkehard.Horner's reply is more apt and can handle situations when the vba is called from a different directory. In that case the CurrentDirectoy obviously won't give the expected path.

这篇关于适用VBS在当前目录到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！