asked on

Convert date

Hi.

I have excel document where the name of the sheets is the date the sheet was created. But some of the sheets have a date format like this: dd/mm/yyyy and I want it like this: yyyy/mm/dd. The name of the sheets in a Listbox is added by using ADOConnection.GetTableNames(ListBox1.Items,false);

How can I convert the dateformat to the right one?

Geert G

in delphi simply use FormatDateTime
FormatDateTime( 'yyyy/mm/dd', DateTimeValue)

can you actually use slashes in the file name, if not :
> FormatDateTime('yyyymmdd', DateTimeValue)

excel ???

QC20N

ASKER

Yes, but how? :)

If you see in line 15 I add TableName to the listbox.

procedure TFrmMain.BtnADOConnectXLSClick(Sender: TObject);
var strConn :  widestring;
begin
  strConn := 'Provider=Microsoft.ACE.OLEDB.12.0;' +
             'Data Source=' + 'C:\path\Project Pro_SE.xlsx' + ';' +
             'Extended Properties="Excel 12.0 xml;HDR=YES";';
  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  ADOConnection1.CursorLocation := clUseServer;
  ADOConnection1.LoginPrompt := false;
  ADOConnection1.Mode := cmShareDenyNone;
  ADOConnection1.Provider := 'Microsoft.ACE.OLEDB.15.0';
  try
    ADOConnection1.Open;
    ADOConnection1.GetTableNames(listbox1.Items,false);
  except
    ShowMessage('Unable to connect to Excel, make sure the workbook exist!');
    raise;
  end;
  AdoQuery1.CursorLocation:=clUseServer;
end;

Open in new window

Geert G

how did the tablenames get in there ?

if you don't change them at that level you'll need:
A > a translation from the orginal format to your new display format
B > that filename won't exist anymore, so you can't do anything with it in the next step

change the original filename formats by setting up a rule when entering the file names.

Geert G

do you have some samples of correct and wrong filenames ?

without those samples, it's guessing at what the interpretation converter should do

QC20N

ASKER

https://www.experts-exchange.com/questions/28363030/Compare-of-Excelsheets.html

Just below the accepted solution in the link there is file. If that is was you are asking. :)

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial