Best Way to Create Log File?

[Jul]

Basically I want to take information from a bunch of PO
files in Excel and extract the information I need into
another file, to create a general list of all the
information.
Right now the only way I can think to do this is to use
Microsoft Query. And the only reasonable way i can think
to do it is actually the opposite to create a list and
then put it into the PO form. (We just want to make it so
that people don't have to enter the data twice.)
Is there an easier way to do this?

 

[Dave Peterson]

One way to build your log of existing po's is to open each of the workbooks and
extract the data. If the po form is nicely laid out (or uses nice range names),
and if the workbooks were in a nice spot--the same folder, you could use a macro
to catch up:

Option Explicit
Sub testme03()

Application.ScreenUpdating = False

Dim myFiles() As String
Dim iCtr As Long
Dim myFile As String
Dim myInPath As String
Dim tempWkbk As Workbook
Dim testWks As Worksheet
Dim myWksName As String
Dim logWks As Worksheet
Dim oRow As Long

Set logWks = Workbooks.Add(1).Worksheets(1)
With logWks
.Name = "Log_" & Format(Date, "yyyymmdd_hhmmss")
.Range("a1:c1").Value _
= Array("WorkbookName", "Error", "A1Value", "b1Value")
End With
oRow = 1

myWksName = "sheet1"

myInPath = "c:\my documents\excel"
If Right(myInPath, 1) <> "\" Then
myInPath = myInPath & "\"
End If

myFile = Dir(myInPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
iCtr = 0
Do While myFile <> ""
iCtr = iCtr + 1
ReDim Preserve myFiles(1 To iCtr)
myFiles(iCtr) = myFile
myFile = Dir()
Loop

If iCtr > 0 Then
For iCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar _
= "Processing: " & myFiles(iCtr) & " at: " & Now
oRow = oRow + 1
logWks.Cells(oRow, 1).Value = myFiles(iCtr)
Set tempWkbk = Nothing
On Error Resume Next
Application.EnableEvents = False
Set tempWkbk = Workbooks.Open(Filename:=myInPath & myFiles(iCtr), _
ReadOnly:=True, UpdateLinks:=0)
Application.EnableEvents = True
On Error GoTo 0
If tempWkbk Is Nothing Then
'couldn't open it for some reason
logWks.Cells(oRow, 2).Value = "Error opening workbook"
Else
Set testWks = Nothing
On Error Resume Next
Set testWks = tempWkbk.Worksheets(myWksName)
On Error GoTo 0
If testWks Is Nothing Then
logWks.Cells(oRow, 2).Value = "Missing sheet"
'do nothing
Else
With testWks
logWks.Cells(oRow, 2).Value = "ok"
logWks.Cells(oRow, 3).Value = .Range("a1").Value
logWks.Cells(oRow, 4).Value = .Range("b1").Value
End With
End If
tempWkbk.Close savechanges:=False
End If
Next iCtr
logWks.UsedRange.Columns.AutoFit
Else
logWks.Parent.Close savechanges:=False
End If

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

Now once you've caught up, you could have the user add the data to the next open
row in the log file.

Then have a macro populate a worksheet (hidden?) in the same workbook and copy
that new sheet out to a new workbook, save it as a nice name, save the changes
to the log workbook and be ready for the next po number.