excel - 将 VBS 脚本转换为 Excel VBA

Question

我目前有 .vbs 文件，双击并运行这些文件，可以将 Excel 文件转换为 .txt 文件，其输出格式如下。我想将其添加为 VBA 代码片段，以便我可以直接从 Excel 内部创建文件。我没有使用 .VBS 的经验，因此事实证明这很困难，而且我没有取得任何进展。

有人有建议或愿意帮助将其转换为 VBA 吗？

感谢任何帮助。

谢谢

wscript.echo "Process Journal Entries for upload?"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("S:\Accounting\JE60.xlsm")
set myfileFSO=CreateObject("Scripting.FileSystemObject")
set myts=myfileFSO.CreateTextFile("S:\Accounting\JE60.txt")
set objWorksheet=objWorkbook.Sheets(1)

introw=1
count=0
Do Until objWorksheet.Cells(intRow, 1).Value = "" 
count=count+1
introw=introw+1
loop

myts.write "G," & objworksheet.cells(6,2).value  & "," & objworksheet.cells(2,2).value & "," & objworksheet.cells(5,2).value & "," &objworksheet.cells(7,2).value & "," &objworksheet.cells(3,2).value & "," &objworksheet.cells(4,2).value & "," & objworksheet.cells(8,2).value & "," & objworksheet.cells(10,2).value & "," & objworksheet.cells(11,2).value & "," & objworksheet.cells(12,2).value & chr(10)    


for i=14 to count

myts.write "D," & objworksheet.cells(i,1).value & "," & objworksheet.cells(9,2).value & "," & objworksheet.cells(i,2).value & "," &objworksheet.cells(5,2).value & "," & objworksheet.cells(i,3).value & "," & objworksheet.cells(i,4).value & "," & objworksheet.cells(i,5).value &"," &objworksheet.cells(i,6).value &"," &objworksheet.cells(i,7).value &"," &objworksheet.cells(i,8).value &"," &objworksheet.cells(8,2).value & chr(10) 

if objworksheet.cells(i,9).value <> "" then
myts.write "A," &objworksheet.cells(i,9).value &"," &objworksheet.cells(i,10).value &"," &objworksheet.cells(i,11).value &"," &objworksheet.cells(i,12).value &"," &objworksheet.cells(i,13).value &"," &objworksheet.cells(i,14).value &"," &objworksheet.cells(i,15).value &"," &objworksheet.cells(i,16).value &"," &objworksheet.cells(i,17).value &"," &objworksheet.cells(i,18).value &"," &objworksheet.cells(i,19).value &"," &objworksheet.cells(i,20).value &"," &objworksheet.cells(i,21).value &"," &objworksheet.cells(i,22).value &"," &objworksheet.cells(i,23).value &"," &objworksheet.cells(i,24).value &"," &objworksheet.cells(i,25).value &"," &objworksheet.cells(i,26).value &"," &objworksheet.cells(i,27).value &"," &objworksheet.cells(i,28).value &"," &objworksheet.cells(i,29).value & chr(10) 

End if

next

myts.close
objExcel.Quit
set objExcel=nothing

' msgbox "Done."
wscript.echo "Done"

Answer 1

它与 VBA 非常接近。将整个内容放入 VBA 模块内的 Sub/End Sub block 中，并一一调试每个错误。

您需要进行的主要更改是:

1) 设置 objExcel = Excel.Application 代替您当前的行。您实际上根本不需要 objExcel，但保留它可以最大限度地减少您需要进行的代码更改量。

2) 如果您已打开工作簿，请编写 Set objWorkbook = Workbooks("JE60.xlsm")

3)删除行objExcel.Quit，否则你将继续丢失你的工作!

4) 将 wsript.echo 替换为 debug.print。