Get info from a file that is already open

[ricowyder]

Dear users,

I have the following macro, which is working fine (surely, there can
be improvement):

Private Sub CommandButtonUpdate_Click()

Dim i As Integer
Dim j As Integer
Dim idString As String
Dim sFilePath As String
Dim NameStaff As String
Dim wbNew As Workbook
Dim wb As Workbook
Set wb = ActiveWorkbook

Application.ScreenUpdating = False

For i = 1 To 32

j = i + 7
idString = ActiveSheet.Range("C" & j).Text
If idString <> "" Then
sFilePath = "L:\Year Planner\PresenceCheck\users\" & idString
Application.DisplayAlerts = False
Workbooks.Open sFilePath
Set wbNew = ActiveWorkbook
wb.Sheets(1).Range("D" & j).Value =
wbNew.Sheets(1).Range("D9").Value
wb.Sheets(1).Range("E" & j).Value =
wbNew.Sheets(1).Range("D20").Value
wbNew.Close
End If
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

My problem: the purpose of this macro is to go into each staffs own
Excel file (all saved on server) to get the info

wb.Sheets(1).Range("D" & j).Value =
wbNew.Sheets(1).Range("D9").Value
wb.Sheets(1).Range("E" & j).Value =
wbNew.Sheets(1).Range("D20").Value

However, our staff has opened these files all the time. As soon as a
file is already opened, the update does not work anymore.
How can I avoid this problem (I guess it is read-only). PLEASE HELP!

Thanks a lot.

Regards,

Rico

 

[Gary Keramidas]

not sure what you're looking for. you can try opening the file as read only to
and see if that helps.

Workbooks.Open sFilePath, ReadOnly:=True

 

[ricowyder]

not sure what you're looking for. you can try opening the file as read only to
and see if that helps.

Workbooks.Open sFilePath, ReadOnly:=True

--

Gary

















- Zitierten Text anzeigen -

Perfect. Thanks a lot. It works.

 

[p45cal]

Another possibility.. the problem seems to arise from having to open the
workbooks. What if the workbooks didn't need to be opend at all? It's well
known that VBA cannot gain access to a cell's value in a workbook that is
closed however a formula in a worksheet can. (On reflection, that's boll**ks
- it's NOT well known.)

='C:\Documents and Settings\All Users\Documents\[aTestFile1.xls]Sheet1'!D4

will work on a closed worksheet - note the square brackets and single
quotes. Couldn't it just be a case of updating the links to grab new data?

If not, and you want to just have the values in the cells rather than a
formula, you could get vba to put the formula in the cell, then convert the
formula to a value:

For i = 1 To 3
'j = i + 7
idString = ActiveSheet.Range("C" & i).Text
If idString <> "" Then
'sFilePath = "='L:\Year Planner\PresenceCheck\users\[" & idString 'slightly
adjusted
Application.DisplayAlerts = False
sFilePath = "='C:\Documents and Settings\All Users\Documents\[" & idString
sFilePath2 = sFilePath & "]Sheet1'!D3"
' problem may arise here if sheet name is changed by staff, or Sheets(1) is
'not called 'Sheet1'; change as required
wb.Sheets(1).Range("D" & i).Formula = sFilePath2
sFilePath2 = sFilePath & "]Sheet1'!D4"
wb.Sheets(1).Range("E" & i).Formula = sFilePath2

'Remove formulae and replace with values
wb.Sheets(1).Range("D" & i).Value = wb.Sheets(1).Range("D" & i).Value
'remove if you're happy to leave formula in place
wb.Sheets(1).Range("E" & i).Value = wb.Sheets(1).Range("E" & i).Value
'remove if you're happy to leave formula in place
End If
Next

I've changed paths and filenames and stuff for reproducing your problem and
testing on my network and the problem seemed to be solved. Rather than
attempt to change things back to your paths and names and so introduce
mistakes, I've chosen to leave it as is.

The only problem I foresee is the name of Sheet(1) in your workbooks. You
use 'Sheets(1)' to establish which sheet you want to look at, so it might be
called anything. In my snippet above you have to use the sheet name (most
likely 'Sheet1' but it may not be), however, if a member of staff changes the
sheet's name it will throw an error. I haven't figured a way of grabbing the
sheet names of a closed workbook.