VBScript激活不带URL的打开的Excel电子表格 [英] VBScript Activate open Excel spreadsheet without URL

问题描述

我有一个项目，该应用程序将用于多个电子表格，并且命名格式总是会有所不同.他们希望我使用 File→从菜单中打开以打开一个窗口，他们可以在其中从桌面上选择电子表格.我已经有了VBScript代码，但是我无法激活工作簿来收集或输入数据.

I have a project where the application will be used for multiple spreadsheets and naming formats are always going to be different. They want me to use File → Open from the menu to open a window where they select the spreadsheet from their desktop. I've got the VBScript code for that, but I can't activate the workbook to gather, or enter, data.

WShell.SendKeys "%FO"

objExcel.Wait (Now + TimeSerial(0, 0, 3))

For i = 1 to 3
   WShell.SendKeys "+{TAB}"
Next

WShell.SendKeys "{HOME}"
WShell.SendKeys "D"
WShell.SendKeys "{ENTER}"

MsgBox "Please select the file you want to open, then click OK to continue.", _
    vbOKOnly+vbSystemModal, "Open File"

WShell.SendKeys "{ENTER}"

Set objWorkbook = ThisWorkbook

objWorkbook.Sheets(1).Activate
objWorkbook.Sheets(1).Cells(1, 1).Value = "Hello"

我希望 Set objWorkbook = ThisWorkbook 激活当前工作簿，而不必使用其名称或URL.有人可以帮我吗?

I want Set objWorkbook = ThisWorkbook to activate the current workbook without having to use its name or URL. Can someone help me with this?

推荐答案

我想出了如何使其工作的方法.我需要浏览所有打开的Excel电子表格，以查看我刚刚打开的电子表格是否可用.注意:这仅适用于打开一个电子表格，即通过打开"对话框打开的电子表格.

I figured out how to get it to work. I needed to cycle through any open Excel spreadsheets to see if the one I just opened was available. NOTE: This only works for one spreadsheet open, which is the one opened by the Open dialog box.

WShell.SendKeys "%FO"

objExcel.Wait (Now + TimeSerial(0, 0, 3))

For i = 1 to 3
    WShell.SendKeys "+{TAB}"
Next

WShell.SendKeys "{HOME}"
WShell.SendKeys "D"
WShell.SendKeys "{ENTER}"

MsgBox "Please select the file you want to open, then click OK to continue.", _
vbOKOnly+vbSystemModal, "Open File"

WShell.SendKeys "{ENTER}"

On Error Resume Next
   Set objExcel = GetObject(,"Excel.Application")
   If Err.Number <> 0 Then ShowError ("Macro failed to get Excel object.")
On Error GoTo 0

objExcel.Wait (Now + TimeSerial(0, 0, 1))

For Each WB In objExcel.Workbooks
    Set objWorkbook = WB
Next

objWorkbook.Sheets(1).Activate
objWorkbook.Sheets(1).Cells(1, 1).Value = "Hello"

这将打开打开的电子表格，并在单元格A1中输入"Hello".

This will take the open spreadsheet and enter "Hello" in cell A1.

这篇关于VBScript激活不带URL的打开的Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！