I'm having issues writing VBA to a new file , or read writing VBA in an existing file. I'm not sure if I'm doing something wrong or some sequence issue regarding settings.
Here is a sample application with 3 methods:
TestReadVBA - works fine
TestReadWriteVBA - fails
TestWriteVBA - fails
program sample;
{$APPTYPE CONSOLE}
{$R *.res}
uses
XLSReadWriteII5,
BIFF_VBA5,
System.SysUtils;
procedure TestReadVBA(const Filename: string);
var
XLS: TXLSReadWriteII5;
ICount, IMacroLines: Integer;
begin
XLS := TXLSReadWriteII5.Create(nil);
try
Xls.ReadVBA := True;
XLS.LoadFromFile(Filename);
for ICount := 0 to (XLS.VBA.Count - 1) do
begin
Writeln('module: ' + XLS.VBA.Modules[ICount].Name);
for IMacroLines := 0 to (XLS.VBA.Modules[ICount].Source.Count - 1) do
begin
Writeln(XLS.VBA.Modules[ICount].Source.Strings[IMacroLines]);
end;
end;
finally
Xls.Free;
end;
end;
procedure TestReadWriteVBA(const Filename: string; const Newfilename: string);
var
XLS, XLS2: TXLSReadWriteII5;
begin
XLS := TXLSReadWriteII5.Create(nil);
try
XLS.ReadVBA := True;
XLS.LoadFromFile(Filename);
Xls.VBA.EditVBA := True;
with Xls.VBA.AddModule('mymodule2', VmtDocument).Source do
begin
Add('Public Function add(a As Integer, b As Integer) As Integer');
Add('add = a + b');
Add('End Function');
end;
Xls.SaveToFile(Newfilename);
finally
Xls.Free;
end;
end;
procedure TestWriteVBA(const Filename: string);
var
XLS: TXLSReadWriteII5;
begin
XLS := TXLSReadWriteII5.Create(nil);
try
Xls.VBA.EditVBA := True; // blows up because XlS.VBA is not initialised
with Xls.VBA.AddModule('mymodule2', VmtDocument).Source do
begin
Add('Public Function add(a As Integer, b As Integer) As Integer');
Add('add = a + b');
Add('End Function');
end;
XLS.SaveToFile(filename);
finally
Xls.Free;
end;
end;
begin
try
// works fine
TestReadVBA('sample.xls');
// problem appending
TestReadWriteVBA('sample.xls', 'sample_with_update.xls');
// problem appending vba to a new file
TestWriteVBA('new_sample.xls');
except
on E: Exception do
Writeln(E.Message);
end;
end.
XLS.Version := xvExcel97;
XLS.ReadVBA := True;
XLS.BIFF.ReadMacros := True;
XLS.VBA.EditVBA := True;
with XLS.VBA.AddModule('mymodule2', VmtDocument).Source do begin
Add('Public Function __SUGGA__(a As Integer, b As Integer) As Integer');
Add('add = a + b');
Add('End Function');
end;
XLS.Filename := 'test.xls';
XLS.Write;
Do I need an update - could you please refer to what I must download? Nothing changed from the reinstallation I mentioned above (and have downloaded twice)
I sent you a mail this Friday and today asking you to send me the XLS file you created.
Please do so. If the code you used to create it not is identical to the
sample I provided, please also include your code.
I got it to do what I needed. with vmtDocument, the code is added in the 'Microsoft Excel Objects' section. with vmtMacro, the code is added in the 'Modules' section as I required.
xlsm
Also, can you advise how to read/add to xlsm?
I tried changing the version to 2007 and the filename, but it did not work.
