Date Modified VFP to EXCEL

[Free0N]

I import VFP (.DBF) files to Excel on a daily basis.

Does anyone know if there is a way to import a timestamp, e.g. the date when the .DBF file was created/modified from file properties or anywhere else and export it into a cell on the excel sheet?

So that I would have the date the database was renewed on, in Excel.

Thank you in advance.

 

[Olaf Doschke]

Well, in first order wouldn't that simply be system time as you do the import? Files have the last write time. FDATE() and ADIR() give that. LUPDATE() can also tell that specifically about DBFs.

But without VFP at hand, only within Excel I don't see unless you automate Scripting.Filesystemobject in VBA to get at file timestamps.

Edit: Excel has something for that, too:

http://www.techonthenet.com/excel/formulas/filedatetime.php

Bye, Olaf.

 

[Free0N]

Olaf, thank you for your answer.

I do have VFP at hand.

I'm not interested in the system time, but rather in the creation date of the .DBF file

Best Regards,
Free0N

 

[Olaf Doschke]

Well the creation date can be quite outdated, the last modification should be more interesting, no matter if we talk about DBFs created daily as export or DBFs part of a DBC having been created years ago and in regular use.

Bye, Olaf.

 

[Mike Lewis]

How are you doing the import? If you are using Excel Automation, you could do something like this:

SELECT TheTable
ldDate = LUPDATE()
loExcel = CREATEOBJECT("excel.application")
loBook = loExcel.Workbooks.Open("The Workbook")
loSheet = loBook.Sheets(1)
loSheet.Cells(1,1).Value = ldDate
loBook.Close
loExcel.Quit

Note that the above isn't meant to be finished code; it's just to give you a rough idea.

Even if you are using some other method to import the DBF, you should be able to apply the above code or something similar.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

And, by the way, welcome to the forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Free0N]

Mike, thank you for your reply.

I am currently using multiple Excel queries for different types of data through

Data - Connections, with the following connection string:

DSN=Visual FoxPro Tables;UID=;;SourceDB=c:\file_name\dbf;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

Command type: SQL

Command text:

SELECT file_name.year, file_name.month, file_name.data, file_name.quantity

FROM file_name
file_name

 

[Olaf Doschke]

Mike also already showed how to use LUPDATE() to set this from the VFP side.
You may try to query LUPDATE(), but then you get this value for all rows.

And to get file creation time, you could use some of the methods described here, though even in the title you talk about Date Modified...
[URL unfurl="true"]http://www.tomorrowssolutionsllc.com/Articles/Retrieving%20a%20file%27s%20creation%20date.PDF[/url]

Bye, Olaf.

 

[JRB-Bldr]

It sounds as though you are trying to do this within Excel rather than within VFP.

Yes, you can do as Mike & Olaf have advised to add LUPDATE() to your SQL Query string

SELECT file_name.year, file_name.month, file_name.data, file_name.quantity, LUPDATE() AS DBF_Updt

But this does not change the resultant Excel file Modified Date to match the DBF Last-modified date.

You could get the DBF Last-Modified date as shown and then use Excel itself (VBA or other) to write that out to the Excel file modified date.

Maybe an Excel support forum area (instead of this VFP language-specific area) would have more specific info on what you need.

Good Luck,
JRB-Bldr

 

[Free0N]

Thank you very much for your answers.

I was simply intending to fetch the 'last modified' date of the .DBF and put it in text format into any cell of the Excel file I am importing the data to.

Best Regards,
Free0N

 

[Olaf Doschke]

Well, there always are many ways in VFP.

Perhaps take [tt]SELECT DISTINCT LUPDATE() FROM file_name[/tt] as the source of a single cell and then this'll just set it to the last modification datetime.

Bye, Olaf.

 

[vgulielmus]

You can also use GetDetailsOf()

LOCAL oShell,loErr as Exception,lcFname,oFolder,oFile
oShell = CREATEOBJECT("shell.application")
lcFname = "F:\Test\book1.xlsx"

oFolder = m.oShell.NameSpace(JUSTPATH(m.lcFname))
?isnull(m.oFolder)
IF !ISNULL(m.oFolder)
	oFile = oFolder.ParseName(JUSTFNAME(m.lcFname))
	IF !ISNULL(m.oFile)
		?m.oFile.ModifyDate
		?'Date created',oFolder.GetDetailsOf(oFile, 4)
		?'Date modified',oFolder.GetDetailsOf(oFile, 3)
		?'Date accessed',oFolder.GetDetailsOf(oFile, 5)
	ELSE
		MESSAGEBOX("File does not exist",16,"Error")
	ENDIF
ELSE
	MESSAGEBOX("Folder does not exist",16,"Error")
ENDIF

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[Olaf Doschke]

Just one question: What is the driver used for the Visual FoxPro Tables DSN? SourceType=DBF and other properties point out you use a VFP ODBC driver, which is fine. You might look for the latest driver at

http://download.microsoft.com/download/vfoxodbcdriver/Install/6.1/W9XNT4/EN-US/VFPODBC.msi

Bye, Olaf.