Read Closed Workbook

[rpunger1957]

I am trying to read cell A1:A1500 on closed workbook C:\TimeCards\CivlianAlphaRoster.xls for loop info on open workbook to save as filename. I have tried for 3 days with no results.

Can someone help to see what I may be doing wrong?

Mega thanks for any help.

Short VBA is below.


Sub TimeCards()
Dim Path As String
Dim DirArray As Variant


' Read CivilianAlpha Roster Cell A
' Read CivilianAlpha Roster Cell A
' Read CivilianAlpha Roster Cell A



strPath = "C:\TimeCards"
strFile = "CivilianAlphaRoster.xls"
strSheet = "Sheet1"
strRng = Range("A1").Address(1, 1, xlR1C1)

strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng

DirArray = Range("A1:A1500").Value

Do Until IsEmpty(ActiveCell)

Path = ExecuteExcel4Macro(strRef)
Application.Wait (Now + TimeValue("00:00:03"))
ActiveCell.Offset(0, 0).Select
Application.Wait (Now + TimeValue("00:00:03"))
ActiveCell.Offset(0, 0).Select

Application.Wait (Now + TimeValue("00:00:03"))
Windows("CivilianAlphaRoster.xls").Activate

PasteMe = (ActiveCell)
Workbooks.Open ThisWorkbook.Path & "" & "2019 AFRC_Timesheet v54.xls"
Windows("2019 AFRC_Timesheet v54.xls").Activate
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Application.Wait (Now + TimeValue("00:00:3"))
Windows("2019 AFRC_Timesheet v54.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:\TimeCards" & PasteMe & " 2019 AFRC_Timesheet v54.xls", FileFormat:=xlExcel8 _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True

Application.Wait (Now + TimeValue("00:00:3"))
Windows("CivilianAlphaRoster.xls").Activate
ActiveCell.Offset(1, 0).Select
A = A + 1
Loop
End Sub

 

[ravishankar]

try this link
https://www.mrexcel.com/forum/excel-questions/437931-extract-data-closed-excel-files-vba.html
ravishankar