How to use VBA to Retrieve Excel File Formats and Worksheet Data and Transfer Summary to Different File

[Al0387]

Hello

I am trying to write a VBA script to Retrieve Excel File Formats (fname, fsize, lastmod, etc.) and certain Worksheet Data (two cell text values) and Transfer the collected information to a Different workbook in a different directory.

Note that the Source Excel files has the target text values in a "hidden" worksheet. So I think the VBA script needs to make the source worksheet Visible to get at the data values.

I can do the above in two separate VBA scripts, but I would rather use one. There are Hundreds of Excel files in scope for this project.

Any help would be greatly appreciated.

Many many Thanks
Al (Al0387)

 

[Al0387]

The following detail is provided to clarify what I am trying to do in this project. I truly appreciate any help with this challenge. If someone have a basic VBA script that I can edit, I would appreciate the HELP... Thanks

I need to create a multi-column UserForm containing 3 columns:
Column 1 = All the Excel File Names from a Specific Directory (There are hundreds of files the Directory)
Column 2 = Customer Name from Cell “F1” from Worksheet (“Sheet1”) from each Excel File in Column 1
Column 3 = LastModified Date for the Excel File in Column 1

The UserForm will Allow the User to Click On the Excel File Name in Column 1, and:
a) Open Excel File, or
b) Print the Excel File to Printer, or
c) Save the Excel File as a PDF file (the File name will be Concatenation of “Customer Name” & “_” & “Excel File Name (without the .xls*)”
Many many Thanks