VBA - Write to Excel File and Name and Save it

I primarily write to csv files and have the down pat..... however i am needing to write to a new excel file, name it and then save it. I can create the file, write the data, however i am stumped on how i name and save it. I am proficeint in VBA and when i try to use VBA code it doesnt work. This has been driving me crazy... any help would greatly appreciated.

Excel = CreateObject("Excel.Application");
Excel.Visible = True;
Workbooks = Excel.Workbooks;
ActiveBook = Excel.ActiveWorkbook;
FileName = "TEST";
Worksheet = Excel.ActiveSheet;
Range = Worksheet.Range("A1", "A1");
Range.Value = 899;
Path = "C:/AMIWEB/CSVData/AllocationFiles/Test.xls";

This isn't really an AmiBroker question, but are you sure you want to be setting SaveChanges to false in the Excel.Workbooks.Close() call? Also, I think your Path variable needs to be passed into the Close call as well.

Here is the jscript code for opening and saving a workbook.

var Path = "C:/AMIWEB/CSVData/AllocationFiles/Test.xls";
var ExcelApp = new ActiveXObject("Excel.Application");
ExcelApp.Application.Visible = true;
ExcelApp.Application.DisplayAlerts = false;

var fso = new ActiveXObject("Scripting.FileSystemObject");
if (fso.FileExists(Path)) {
	WScript.echo("file exists.");
	var workbook = ExcelApp.Workbooks.Open(Path);
	var worksheet = workbook.Worksheets("Sheet1");Here 
} else {
	WScript.echo("file not exists.");
	var workbook = ExcelApp.Workbooks.Add;
	var worksheet = workbook.Worksheets("Sheet1");

worksheet.Cells(1,1) = 899;

Some often used code.

var xlUp = -4162;
var xlToLeft = -4159;
//find last occupied row in column A
var ttl_row = worksheet.Cells(worksheet.Rows.Count,1).End(xlUp).Row
//find last occupied column in row 1
var ttl_col = worksheet.Cells(1,worksheet.Columns.Count).End(xlToLeft).Column
//do something while column A is not empty
var i = 1;
while (worksheet.Cells(i,1).text.length > 0) {
	//do something

@mfu5324 - the code you posted is NOT AFL (no := operator in AFL), and is it likely NOT VBA either (don't have semicolons in VBA).

Also if you want to save, just call Save function


Also note that paths in AFL need to be written as follows:

Path = "C:\\AMIWEB\\CSVData\\AllocationFiles\\Test.xls";
1 Like

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.