Excel VBA - 打开工作簿并粘贴数据

Question

我发现了这个优秀的代码，但我需要根据自己的目的调整它。

首先，我需要打开我们网络上的数据工作簿。我遇到的问题是有时可能会被其他用户打开，并提供“只读”选项。我怎样才能让它接受只读选项，以便我可以开始提取数据。

其次它使用“=”进行复制。我怎样才能改变它只复制值？

首先宏：

Sub test() 
'to open another workbook 
Application.ScreenUpdating = False 
Workbooks.Open Filename:=ThisWorkbook.Path & "\Schedule.xls" 
ThisWorkbook.Activate 
Application.ScreenUpdating = True 

End Sub 

第二个宏：

Dim Sh As Worksheet 
Dim Newsh As Worksheet 
Dim myCell As Range 
Dim ColNum As Integer 
Dim RwNum As Long 
Dim Basebook As Workbook 

With Application 
    .Calculation = xlCalculationManual 
    .ScreenUpdating = False 
End With 

'Delete the sheet "Summary-Sheet" if it exist 
Application.DisplayAlerts = False 
On Error Resume Next 
ThisWorkbook.Worksheets("Summary-Sheet").Delete 
On Error GoTo 0 
Application.DisplayAlerts = True 

'Add a worksheet with the name "Summary-Sheet" 
Set Basebook = ThisWorkbook 
Set Newsh = Basebook.Worksheets.Add 
Newsh.Name = "Summary-Sheet" 

'The links to the first sheet will start in row 2 
RwNum = 1 

For Each Sh In Basebook.Worksheets 
    If Sh.Name <> Newsh.Name And Sh.Visible Then 
     ColNum = 1 
     RwNum = RwNum + 1 
     'Copy the sheet name in the A column 
     Newsh.Cells(RwNum, 1).Value = Sh.Name 

     For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the range 
      ColNum = ColNum + 1 
      Newsh.Cells(RwNum, ColNum).Formula = _ 
      "='" & Sh.Name & "'!" & myCell.Address(False, False) 
     Next myCell 

    End If 
Next Sh 

Newsh.UsedRange.Columns.AutoFit 

With Application 
    .Calculation = xlCalculationAutomatic 
    .ScreenUpdating = True 
End With 
End Sub 

Answer 1

你总是可以打开为只读，如果你只提取数据的工作簿。 而不是使用.formula使用价值