通过活动工作簿中的文本打开保存在桌面上的工作簿 [英] Open workbook saved on desktop via text in active workbook

问题描述

我的桌面上保存了一个文件。文件名的末尾将始终相同（"asreported.xls"）。但是，文件名的开头将有一个代码前缀（即：PPG，COST，O，D，T等......）。 

I have a file saved on my desktop. The end of the file name will always be the same ("asreported.xls"). However, the start of the file name will have a ticker prefix (ie: PPG, COST, O, D, T, etc...). 

我想定义我的活动工作簿中名为"开始"的工作表上的文件名。在"开始"上A1中的表格我可以输入"c：\ usersrs \\\\\\\\\\\\\\\\\\\\\\\\\\\所以当我运行我的VBA代码时，它将知道打开哪个文件
。然后将该文件中的工作表复制到我的活动工作簿中，工作表名为"Mergent"。 

I would like to define the file name in my active workbook on a sheet called "Start". On the "Start" sheet in A1 I can put "c:\users\name\desktop\tmp\ppg_asreported.xls" so that when I run my VBA code, it will know which file to open. The sheet from that file is then copied into a my active workbook and the sheet is named "Mergent". 

我当前的VBA编号为： 

My current VBA cod is: 

Public Sub OpenStockRowBS()

' *************************************************

' Define Workbook and Worksheet Variables

' *************************************************

Dim wkbMyWorkbook As Workbook

Dim wkbWebWorkbook As Workbook

Dim wksWebWorkSheet As Worksheet

Dim wsDest As Worksheet



Set wkbMyWorkbook = ActiveWorkbook

On Error Resume Next

Set wsDest = wkbMyWorkbook.Worksheets("Mergent")

wsDest.Cells.ClearContents

On Error GoTo 0



' *************************************************

' Open The Web Workbook From Desktop (Financial Statements)

' *************************************************

Workbooks.Open
("C:\Users\name\Desktop\tmp\asreported.xls")



' *************************************************

' Set the Web Workbook and Worksheet Variables

' *************************************************

Set wkbWebWorkbook = ActiveWorkbook

Set wksWebWorkSheet = ActiveSheet



' *************************************************

' Copy The Web Worksheet To My Workbook and Rename

' *************************************************

If wsDest Is Nothing Then

    wksWebWorkSheet.Copy
After:=wkbMyWorkbook.Sheets("Start")

   
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "Mergent"

Else

    wksWebWorkSheet.Cells.Copy
wsDest.Range("A1")

End If



' *************************************************

' Close the Web Workbook

' *************************************************

wkbMyWorkbook.Activate

wkbWebWorkbook.Close

End Sub

我尝试将C：\ users .....代码行更改为： 

I have tried changing the C:\users..... code line to: 

Workbooks.Open（&C） ：\ Users \ name \Desktop \tmp \"& thisworkbook.Worksheets（" Start"）。Range（" A7"）"）

Workbooks.Open ("C:\Users\name\Desktop\tmp\"& thisworkbook.Worksheets("Start").Range("A7")")

为了使代码能够反映我的"开始"中名称的更改。片群组。我无法获取数据。 

In an effort to get the code to reflect changes in the name on my "Start" sheet.  I can't get the data to pull. 

任何想法？提前致谢！

推荐答案

如果文件名为" ppg_asreported.xls " ;保存在名为" tmp "的文件夹中在您的桌面上，值" ppg "在工作表开始上的
A7 ，尝试这样的事情......

If the file called "ppg_asreported.xls" is saved in a folder called "tmp" on your Desktop and the value "ppg" is in A7 on Sheet Start, try something like this...

Public Sub OpenStockRowBS()

' *************************************************
' Define Workbook and Worksheet Variables
' *************************************************
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet
Dim wsDest As Worksheet
Dim strFileName As String, strFilePath As String

Set wkbMyWorkbook = ActiveWorkbook

On Error Resume Next
Set wsDest = wkbMyWorkbook.Worksheets("Mergent")
wsDest.Cells.ClearContents
On Error GoTo 0

' *************************************************
' Open and set the Web Workbook From Desktop (Financial Statements)
' *************************************************
strFileName = wkbMyWorkbook.Worksheets("Start").Range("A7").Value & "_asreported.xls"
strFilePath = Environ("UserProfile") & "\Desktop\tmp\"

'Check if the file exists
If Len(Dir(strFilePath & strFileName)) = 0 Then
    MsgBox "The file " & strFileName & " was not found at the location " & strFilePath & ".", vbCritical, "File Not Found!"
    Exit Sub
End If
Set wkbWebWorkbook = Workbooks.Open(strFilePath & strFileName)

' *************************************************
' Set the Web Worksheet Variables
' *************************************************

Set wksWebWorkSheet = ActiveSheet

' *************************************************
' Copy The Web Worksheet To My Workbook and Rename
' *************************************************
If wsDest Is Nothing Then
    wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets("Start")
    wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "Mergent"
Else
    wksWebWorkSheet.Cells.Copy wsDest.Range("A1")
End If

' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
End Sub

这篇关于通过活动工作簿中的文本打开保存在桌面上的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！