reflection in excel

[JIGYANSHA1985]

Sir,
Please do me a favour in providing me a macro.

I am attaching a file tour.xls with Some .pdf files are also attached.

I need a macro which should import the file names to the sheet visit in tour.xls files excluding .pdf extension from "d:\tour\pdf\*.pdf" .

When a Macro for update records runned, it will do the following things ..

1> For Ex. File name is 1187-06112010-8350.
On Visit sheet 3 columns are there A = BRAN, B = DATE, C = EMPCODE.
Here taking into account the above example 1187 shall be placed at column A, 06/11/2010 at column B and 8350 at Column C.

2> Again there are two other sheets are there in the same file ... i.e. reflect-bran and reflect-emp.
In these sheet I want to reflect year+month-wise visit details. Again BRAN and EMPCODE wise..
For ex. in REFLECT-BRAN sheet no of BRAN visited shall be reflected taking into account the BRAN & VISIT sheet.
Like that in REFLECT-EMP sheet no of EMPCODE visited shall be reflected taking into account the EMP & VISIT sheet.

Each time when the macro shall be run, it directly takes the file name from the root and separate it column-wise.

Thereafter, taking into account the BRAN & VISIT sheet update the REFLECT-BRAN status. Like-wise REFLECT-EMP shall be

updated taking into account the EMP & VISIT sheet.

Regards

[HansV]

Should the existing data in the Visit sheet be cleared at the start of the code, or should new rows be added below the existing data?

[JIGYANSHA1985]

Sir,

Clears all record from the Visit sheet and update new records each time from the beginning ...

Regards

[HansV]

Here is code for the UPDATE RECORDS button for the first part (filling the Visit sheet) - please test it on a copy of the workbook:

Code: Select all

Private Sub UPDATE_Click()
  Dim strPath As String
  Dim strFile As String
  Dim r As Long
  Dim strParts() As String
  ' Get the path
  strPath = ThisWorkbook.Path
  If Right(strPath, 1) <> "\" Then
    strPath = strPath & "\"
  End If
  ' Clear current data
  Range("A2:C" & Rows.Count).ClearContents
  ' Loop through files
  r = 1
  strFile = Dir(strPath & "*.pdf")
  Do While strFile <> ""
    r = r + 1
    strFile = Left(strFile, Len(strFile) - 4)
    strParts = Split(strFile, "-")
    Range("A" & r) = Val(strParts(0))
    Range("B" & r) = DateSerial(Right(strParts(1), 4), Mid(strParts(1), 3, 2), Left(strParts(1), 2))
    Range("B" & r).NumberFormat = "m/d/yyyy"
    Range("C" & r) = Val(strParts(2))
    strFile = Dir
  Loop
End Sub

[JIGYANSHA1985]

Sir,
Its only clearing old records from visit_sheet but not updating new records (Path is at the d:\tour\pdf\*.pdf) ...

[HansV]

I had assumed that the workbook would be in the same folder as the PDF files. To specify the path explicitly, use

Code: Select all

Private Sub UPDATE_Click()
  Dim strPath As String
  Dim strFile As String
  Dim r As Long
  Dim strParts() As String
  ' Get the path
  strPath = "D:\Tour\PDF\"
  ' Clear current data
  Range("A2:C" & Rows.Count).ClearContents
  ' Loop through files
  r = 1
  strFile = Dir(strPath & "*.pdf")
  Do While strFile <> ""
    r = r + 1
    strFile = Left(strFile, Len(strFile) - 4)
    strParts = Split(strFile, "-")
    Range("A" & r) = Val(strParts(0))
    Range("B" & r) = DateSerial(Right(strParts(1), 4), Mid(strParts(1), 3, 2), Left(strParts(1), 2))
    Range("B" & r).NumberFormat = "m/d/yyyy"
    Range("C" & r) = Val(strParts(2))
    strFile = Dir
  Loop
End Sub

[JIGYANSHA1985]

Sir,

This time the macro runs perfectly & gives output for the first portion ... If reflection in REFLECT-BRAN & REFLECT-EMP sheet shall be made my problem could be solved ...

Currently if the macro was runned in the visit sheet answers reflected in the same sheet but if any other sheet in the same file was opened answers reflected in that portion ?

Regards

[HansV]

I'll look at it later today.

[HansV]

You have placed the code in the ThisWorkbook module, but that is not a good idea. If you want it to be an ordinary macro, you should place it in a standard module.
In the attached version, I have moved the code to a module, and made it work on the VISIT sheet regardless of where it is called from.

I know that it is not what you asked, but I would use pivot tables to display the count of visits per month - see the VISIT sheet. If you wish, you can use this to populate the REFLECT sheets, but I'm not going to do that for you.

tour1.xls

[JIGYANSHA1985]

Sir,
Thanks a lot ... What you have done for me is really a appreciable task ... I am very much grateful to you ... No matter what was left for me, But what has been done is really a good task and you have spent so much time for me. Thanks to you once again.
Its not just a simple thanks but a thanks from the core of my heart ... I' ll pray before God that you like person live a long & prosperous life and always continue to impart your knowledge like a true volunteer ...
Regards

[HansV]

Thank you very much for your kind words!