Passing file path in Delphi from TOpenDialog as a string

Question

I'm trying to make use of the TOpenDialog in order to pass the path to selected file to the AdoConection and load the content of the Excel file to the table. I'm currently attempting the code below but the last part of the code does not connect to the Excel returning an error: [dcc32 Error] sample_map.pas(80): E2010 Incompatible types: 'string' and 'TOpenDialog'

procedure TForm1.Button1Click(Sender: TObject);
var
  openDialog : TOpenDialog;    // Open dialog variable
  strConn : WideString; // Declare wide string for the connection

begin
  // Create the open dialog object - assign to our open dialog variable
  openDialog := TOpenDialog.Create(self);

  // Set up the starting directory to be the current one
  openDialog.InitialDir := GetCurrentDir;

  // Only allow existing files to be selected
  openDialog.Options := [ofFileMustExist];

  // Allow only .dpr and .pas files to be selected
  openDialog.Filter :=
    'Excel 2003 and older|*.xls|Excel 2007 and older|*.xlsx';

  // Select pascal files as the starting filter type
  openDialog.FilterIndex := 2;

  // Display the open file dialog
  if openDialog.Execute
  then ShowMessage('File : '+openDialog.FileName)
  else ShowMessage('Open file was cancelled');

  // Free up the dialog
  openDialog.Free;

  // Connect the Excel file
    strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
                 'Data Source=' + openDialog + ';' +
                 'Extended Properties=Excel 8.0;';
        AdoConnection1.Connected:=False;
        AdoConnection1.ConnectionString:=strConn;
end;

Answer 1

openDialog is an instance of a file dialog. It is not a string. You need to read the FileName property of the file dialog object like this:

openDialog.FileName

In fact you already use that in one of your ShowMessage calls.

Do make sure that you read this property before calling Free, a mistake present in the code in the question.

In fact you do need to get in to the habit of using try/finally to protect resources. Any time you create an instance of a class you need to make sure that it will be destroyed even in the face of an exception. In your code you need to write it like this:

openDialog := TOpenDialog.Create(self);
try
  .... // use openDialog to let user choose file:
  strConn := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
             'Data Source=' + openDialog.FileName + ';' +
             'Extended Properties=Excel 8.0;';
finally
  openDialog.Free; // executes no matter what, even if exception raised, etc.
end;

I also don't think you need to use WideString here. If you use a Unicode Delphi then you can use the native string type which is an alias for UnicodeString. If your Delphi is pre-Unicode, then you can also safely use string, an alias for AnsiString in that case. The literals you use are ASCII. The file dialog is an ANSI control and so openDialog.FileName is also ANSI. Nothing to be gained using WideString.

Finally, you are mixing up, all in one function, code to select a filename, and code to work on a database connection. It is better to separate concerns. Create a method that simply returns a filename, obtained by letting the user choose through a dialog. And add a method to work on the database connection, that is passed a filename as a parameter.

Answer 2

You need to get the OpenDialog.FileName prior to freeing the dialog:

OpenDialog := TOpenDialog.Create(nil);
try
  // Set up the OpenDialog as before

  // Display the open file dialog
  if openDialog.Execute then
  begin
    strConn := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
               'Data Source=' + openDialog.FileName + ';' +
               'Extended Properties=Excel 8.0;';
    // Connect the Excel file
    AdoConnection1.Connected:=False;
    AdoConnection1.ConnectionString:=strConn;
  else 
    ShowMessage('Open file was cancelled');
finally
  // Free up the dialog
  openDialog.Free;
end;

Of course, you're working much too hard. The Dialogs unit has a much easier way to do this using the PromptForFilename function, which eliminates the need to create and free the dialog entirely:

var
  FileName: string;
begin

  FileName := '';
  if PromptForFileName(FileName,                          // Chosen filename holder
                      'Excel 2003 and older|*.xls|Excel 2007 and older|*.xlsx';  // Filter(s) (optional)
                      '.xlsx',                            // Default extension (opt)
                      'Choose file',                     // Dialog title (opt)
                      GetCurrentDir,                     // Initial dir (opt)
                      False) then                        // Is it a save dlg? (opt)
  begin
    strConn := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
               'Data Source=' + FileName + ';' +
               'Extended Properties=Excel 8.0;';
    // Connect the Excel file
    AdoConnection1.Connected:=False;
    AdoConnection1.ConnectionString:=strConn;
  end
  else
    ShowMessage('Dialog cancelled.');
end;

As a side note, in case you don't know: You can select all Excel files (both .xls and .xlsx) with a single filter if you enter it as .xls*, as in Excel Files|*.xls*.

And, as David says, the best way would be to separate out the code that gets the filename and the code that does the connection as separate functions. Call the first to get the filename, and then pass that filename to the second to actually do the connecting if a filename is selected.