[ HELP ] Word File Creation Date, Modify Date and Author

[technician12]

Hello.

any real savvy VBA pros, who can help me wih this task?
see attched picture for details.

[Kenneth Hobs]

Welcome to the forum!

In a Module, paste the code below. Add the Reference as shown in the comment, or use late binding instead as shown in comments. If you don't know how to add references, you should learn so ask if needed.

The function allows 2 inputs so you only need 1 UDF rather than 3. The comment before the function shows how to call it as a cell formula.

'=fileproperty(A1,1)Function FileProperty(pfn$, p%)
  Dim pr
  On Error GoTo TheEnd
  'Late bound
  'dim fs as Object
  'Set fs = CreateObject("Scripting.FileSystemObject")
  
  'Early bound
  Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
  Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
  Dim fs As Scripting.FileSystemObject
  Set fs = New FileSystemObject
  
  pr = ""
  Select Case True
    Case p = 1: pr = fs.GetFile(pfn).DateCreated
    Case p = 2: pr = fs.GetFile(pfn).DateLastAccessed
    Case p = 3: pr = fs.GetFile(pfn).DateLastModified
    Case Else:
  End Select
  Set fs = Nothing
TheEnd:
  FileProperty = pr
End Function

[technician12]

been trying to get it to work.
i'm not able to install anything on my work computer sadly ( strict company policy ).

any other way to get this to work ?

edit:
Imgur.com/a/EHHWizW

[Kenneth Hobs]

You can attach files. Click the Go Advanced button in lower right of a reply. Then click the Manage Attachments link below that box. From there, it is just browse and upload.

There are many tutorials that explain how to add a Module and add references.

1. https://www.tutorialspoint.com/vba/vba_quick_guide.htm
a. Ctrl+F and search for Module there.
b. Paste the code that I created for you in a new Module.
2. Ctrl+F in that same link and look for Reference.
a. Add the Microsoft Scripting Runtime reference as I commented.

I could make the reference late bound but you would not get the benefit of early binding. Early binding, most always lets intellisense work for that object. That makes learning the methods and properties easy. If it comes down to it, I can comment, uncomment the lines as I explained in the code comments. It is best to learn this simple thing though.
Use the UDF in as cell as I commented in the first line.

Lastly, you will likely need to pass the fullname with path to the function. Your last example did not have a path. If you want to use the path for the document with that macro, you can code the Function as such.

[technician12]

i'm gonna do some reading up on VBA basics, sadly i cannot attact the excel file, sadly due to company file shareing policy again.

btw, thank you ALOT for trying to help a complete beginner

[Kenneth Hobs]

One can easily get around a company restriction by reducing a problem to one step. That is how I solve problems anyway.

I have attached the example that I worked on. Change the value in A1 to an existing drive:\path\filename.ext.

[technician12]

you're a lifesaver! it works!

now i just need to figure out how to throw author into the mix.
the i think i can do the rest myself.

side question.
do you have anywhere you can reccomend, where a beginner can start to learn VBA at about entry-level ?

[Kenneth Hobs]

The link that I posted looks pretty good to me. Just search for "Excel VBA Tutorial" and you will find many. Excel MVP's sometimes have good tutorials and most will have good to great VBA examples. https://www.mvps.org/links.html#Excel

A few of those and more: cpearson.com, https://www.rondebruin.nl/, https://chandoo.org/wp/excel-vba/videos/, youtube.com, etc. Of course many of the forums have decent tutorials. The forums are great to learn by example. Trying to help others is a great way to learn even more.

Recording a macro is a good place to start. After that, go beyond: http://www.tushar-mehta.com/excel/vb...rder/index.htm

GetDetailsOf is a good way to read Window's file properties. Other methods are needed for Excel built-in properties.

'=fileproperty(A1,1)'=fileproperty(A1,4) 
'Author(s) in Windows, not Excel...
Function FileProperty(pfn$, p%)
  Dim pr, o As Object
  
  On Error GoTo TheEnd
  'Late bound
  'dim fs as Object
  'Set fs = CreateObject("Scripting.FileSystemObject")
  
  'Early bound
  Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
  Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
  Dim fs As Scripting.FileSystemObject
  Set fs = New FileSystemObject
  
  pr = ""
  Select Case True
    Case p = 1: pr = fs.GetFile(pfn).DateCreated
    Case p = 2: pr = fs.GetFile(pfn).DateLastAccessed
    Case p = 3: pr = fs.GetFile(pfn).DateLastModified
    Case p = 4
      Set o = CreateObject("shell.application").Namespace(fs.GetFile(pfn).ParentFolder & "\")
      pr = o.GetDetailsOf(o.Items.Item(fs.GetFile(pfn).Name), 20)
    Case Else:
  End Select
  Set fs = Nothing
TheEnd:
  FileProperty = pr
End Function

Here is a list that I made several years ago for Windows File Properties.

0 Name
1 Size
2 Item type
3 Date modified
4 Date created
5 Date accessed
6 Attributes
7 Offline status
8 Offline availability
9 Perceived type
10 Owner
11 Kind
12 Date taken
13 Contributing artists
14 Album
15 Year
16 Genre
17 Conductors
18 Tags
19 Rating
20 Authors
21 Title
22 Subject
23 Categories
24 Comments
25 Copyright
26 #
27 Length
28 Bit rate
29 Protected
30 Camera model
31 Dimensions
32 Camera maker
33 Company
34 File description
35 Program name
36 Duration
37 Is online
38 Is recurring
39 Location
40 Optional attendee addresses
41 Optional attendees
42 Organizer address
43 Organizer name
44 Reminder time
45 Required attendee addresses
46 Required attendees
47 Resources
48 Meeting status
49 Free/busy status
50 Total size
51 Account name
52 Task status
53 Computer
54 Anniversary
55 Assistant's name
56 Assistant's phone
57 Birthday
58 Business address
59 Business city
60 Business country/region
61 Business P.O. box
62 Business postal code
63 Business state or province
64 Business street
65 Business fax
66 Business home page
67 Business phone
68 Callback number
69 Car phone
70 Children
71 Company main phone
72 Department
73 E-mail address
74 E-mail2
75 E-mail3
76 E-mail list
77 E-mail display name
78 File as
79 First name
80 Full name
81 Gender
82 Given name
83 Hobbies
84 Home address
85 Home city
86 Home country/region
87 Home P.O. box
88 Home postal code
89 Home state or province
90 Home street
91 Home fax
92 Home phone
93 IM addresses
94 Initials
95 Job title
96 Label
97 Last name
98 Mailing address
99 Middle name
100 Cell phone
101 Nickname
102 Office location
103 Other address
104 Other city
105 Other country/region
106 Other P.O. box
107 Other postal code
108 Other state or province
109 Other street
110 Pager
111 Personal title
112 City
113 Country/region
114 P.O. box
115 Postal code
116 State or province
117 Street
118 Primary e-mail
119 Primary phone
120 Profession
121 Spouse/Partner
122 Suffix
123 TTY/TTD phone
124 Telex
125 Webpage
126 Content status
127 Content type
128 Date acquired
129 Date archived
130 Date completed
131 Device category
132 Connected
133 Discovery method
134 Friendly name
135 Local computer
136 Manufacturer
137 Model
138 Paired
139 Classification
140 Status
141 Client ID
142 Contributors
143 Content created
144 Last printed
145 Date last saved
146 Division
147 Document ID
148 Pages
149 Slides
150 Total editing time
151 Word count
152 Due date
153 End date
154 File count
155 Filename
156 File version
157 Flag color
158 Flag status
159 Space free
160 Bit depth
161 Horizontal resolution
162 Width
163 Vertical resolution
164 Height
165 Importance
166 Is attachment
167 Is deleted
168 Encryption status
169 Has flag
170 Is completed
171 Incomplete
172 Read status
173 Shared
174 Creators
175 Date
176 Folder name
177 Folder path
178 Folder
179 Participants
180 Path
181 By location
182 Type
183 Contact names
184 Entry type
185 Language
186 Date visited
187 Description
188 Link status
189 Link target
190 URL
191 Media created
192 Date released
193 Encoded by
194 Producers
195 Publisher
196 Subtitle
197 User web URL
198 Writers
199 Attachments
200 Bcc addresses
201 Bcc
202 Cc addresses
203 Cc
204 Conversation ID
205 Date received
206 Date sent
207 From addresses
208 From
209 Has attachments
210 Sender address
211 Sender name
212 Store
213 To addresses
214 To do title
215 To
216 Mileage
217 Album artist
218 Album ID
219 Beats-per-minute
220 Composers
221 Initial key
222 Part of a compilation
223 Mood
224 Part of set
225 Period
226 Color
227 Parental rating
228 Parental rating reason
229 Space used
230 EXIF version
231 Event
232 Exposure bias
233 Exposure program
234 Exposure time
235 F-stop
236 Flash mode
237 Focal length
238 35mm focal length
239 ISO speed
240 Lens maker
241 Lens model
242 Light source
243 Max aperture
244 Metering mode
245 Orientation
246 People
247 Program mode
248 Saturation
249 Subject distance
250 White balance
251 Priority
252 Project
253 Channel number
254 Episode name
255 Closed captioning
256 Rerun
257 SAP
258 Broadcast date
259 Program description
260 Recording time
261 Station call sign
262 Station name
263 Summary
264 Snippets
265 Auto summary
266 Search ranking
267 Sensitivity
268 Shared with
269 Sharing status
270 Product name
271 Product version
272 Support link
273 Source
274 Start date
275 Billing information
276 Complete
277 Task owner
278 Total file size
279 Legal trademarks
280 Video compression
281 Directors
282 Data rate
283 Frame height
284 Frame rate
285 Frame width
286 Total bitrate

[Paul_Hossler]

MS has changed the index number for File Properties as they change O/Ss

Sample:

Capture.JPG


As far as I know, the attached list is complete, but no guarantees

[technician12]

i thought the next part would be easier.
any simple way to do this?

If I3 = "Yes"
then move file in "E3" to Folder from B7 + \Approved - Ex H:\VBA\Approved
then afterward remove "Yes" from I3 ( so it doesent move all )

and if the folder doesent exist, then create it.

[Kenneth Hobs]

Please start a new thread for new problems.