如何自動執(zhí)行將 Excel 工作表保存為 HTML 文件中使用 Visual c + +
http://support.microsoft.com/kb/199691/zh-cn
VC操作Excel，當用SaveAs保存Excel時，如果待保存路徑下已經(jīng)存在該Excel，則會彈出一個對話框提示你當前路徑下已經(jīng)存在該Excel，是否替換，下面有三個按鈕：“是”、“否”、“取消”。如果選擇了“取消”則會彈出一個錯誤警告對話框。
可以按照如下方法解決這個問題：在SaveAs之前添加代碼： _Application ExcelApp; ExcelApp.SetAlertBeforeOverwriting(FALSE); ExcelApp.SetDisplayAlerts(FALSE);
在Office2003版Office所生成的Excel.cpp文件中，類_Workbook的SaveAs函數(shù)，其函數(shù)原型如下： void SaveAs(const VARIANT& Filename, const VARIANT& FileFormat, const VARIANT& Password, const VARIANT& WriteResPassword, const VARIANT& ReadOnlyRecommended, const VARIANT& CreateBackup, long AccessMode, const VARIANT& ConflictResolution, const VARIANT& AddToMru, const VARIANT& TextCodepage, const VARIANT& TextVisualLayout, const VARIANT& Local);
參數(shù)含義參考如下： Question: Nothing like trying to learn/teach yourself something new to make you humble. Winbatch 99p, Excel 97. I'm loading a tab delimited file {M:\somedir\myfile.txt} into Excel using OLE to manipulate it some & want to save it as an Excel .xls file {M:\somedir\myfile.txt}. I can get the file to save under the new name, i.e. with the .xls extension but it's still in tab delimited format, NOT Excel's native file format. The following is from a macro I recorded while doing the action I want to automate: ActiveWorkbook.SaveAs FileName:="M:\TMI_Data\Processed\FEB00.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False I can get the following code to save the file and add the filename to the MRU list. fileXL ="M:\TMI_Data\Processed\FEB00.xls" Awkbk=ObjXL.ActiveWorkbook savefile=Awkbk.SaveAs (fileXL) ; this works OR savefile=Awkbk.SaveAs ( fileXL, , , , , ,@True ) ; this works the @True adds the file to the MRU list. But, whenever I try to insert something in the position that I think the fileformat stuff is supposed to go I get 1261 OLE exception errors or 3250 OLE Object error : Problem occurred formatting parameters. I'm wondering if it's a Named parameter ? If I'm understanding the docs correctly (big IF) a named parameter would go after all the positional parameters ? T/F ? I've tried a lot of permutations & combinations & haven't stumbled up on something that'll work yet. How does one differentiate between /tell one from the other on Named vice positional parameters ? I've looked in the VBA help & haven't stumbled onto anything. Answer: Sounds like you almost have it. Positional parameters first, then the :: then the parameter=value pairs for the named parameters. Question (cont'd): I'm just not grasping something here. I've tried the line : savefile=Awkbk.SaveAs ( fileXL, , , , , , , , , , ::FileFormat = "xlNormal") with 0 to 10 commas for "positional parameters" between the "fileXL" & the "::". With 0 or 1 comma I get 1261 OLE exception Error & the following entry from wwwbatch.ini [OLE Exception] Microsoft Excel=Unable to get the SaveAs property of the Workbook class With 2 to 10 commas I get NO ERRORS, BUT while it saves with an .xls extension it is still Tab delimited. How can you tell if a parameter is a "Named Parameter" ? The VBA docs make the stuff all look like "Positional parameters" Answer: Maybe there are three required parameters? Maybe xlNormal is not a tring but a constant and we have to figure out what number it is? Maybe cut and paste the SaveAs documentation here and we can stare at it. Bit of an OLE tip that I found a bit by accident. If you want to know whether something is a string or a constant, do it in VBA - in this case, something like var=xlNormal If it bombs out, it's a string. If it doesn't, it'll return a value for you to plug into your scripts. Here's the docs for the "SaveAs Method" clipped direct from the VBA help : Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file. Syntax 1 expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout) Syntax 2 expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout) expression Required. An expression that returns a Chart or Worksheet object (Syntax 1) or a Workbook object (Syntax 2). Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don't, Microsoft Excel saves the file in the current folder. FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. Password Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file. WriteResPassword Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only. ReadOnlyRecommended Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only. CreateBackup Optional Variant. True to create a backup file. AccessMode Optional Variant. The workbook access mode. Can be one of the following XlSaveAsAccessMode constants: xlShared (shared list), xlExclusive (exclusive mode), or xlNoChange (don't change the access mode). If this argument is omitted, the access mode isn't changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method. ConflictResolution Optional Variant. Specifies the way change conflicts are resolved if the workbook is a shared list. Can be one of the following XlSaveConflictResolution constants: xlUserResolution (display the conflict-resolution dialog box), xlLocalSessionChanges (automatically accept the local user's changes), or xlOtherSessionChanges (accept other changes instead of the local user's changes). If this argument is omitted, the conflict-resolution dialog box is displayed. AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False. TextCodePage Optional Variant. Not used in U.S. English Microsoft Excel. TextVisualLayout Optional Variant. Not used in U.S. English Microsoft Excel. Resolution: GREAT Tip -- I inserted your line into the macro & then stepped thru it. It returned a value of -4143. I plugged it into the command like so: savefile=Awkbk.SaveAs ( fileXL, -4143 , , , , ,@True ) and SHAZAM it works ! It would have been A WHILE before I'd have stumbled on to that. Thanks Again.
參考鏈接： http://topic.csdn.net/t/20050121/15/3743958.html http://topic.csdn.net/t/20050324/11/3876932.html
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
本人參考網(wǎng)上做法，利用模板實現(xiàn)了VC對EXCEL的操作，但是存在以下問題：
1.第一次運行程序，點擊保存時沒問題，當?shù)诙芜\行程序時，會彈出對話框，提示已經(jīng)存在文件，是否替換（是、否、取消），當點擊“否”的時候會彈出警告對話框，程序中斷；
2.程序運行后，第一次點擊保存時，不會出錯，接著再次點擊保存時，就會出現(xiàn)警告：無法找到模板......；即程序運行后不能實現(xiàn)多次保存。
3.程序在關(guān)閉后，為何在任務管理器的進程里并沒有結(jié)束，即不能實現(xiàn)程序在后臺也關(guān)閉。
以下是源代碼，有勞哪位大俠解惑！不勝感激！
void CTEST::OnConnect()
{
// TODO: Add your control notification handler code here
_Application _app;
_Workbook _workBook;
_Worksheet _workSheet;
Worksheets workSheets;
Workbooks workBooks;
Range range;
_Application ExcelApp;
ExcelApp.SetAlertBeforeOverwriting(FALSE);
ExcelApp.SetDisplayAlerts(FALSE);
// LPDISPATCH lpDisp;
char path[MAX_PATH];
//VARIANT _variant_t;
if (CoInitialize(NULL) != 0)
{
AfxMessageBox("初始化COM支持庫失敗!");
exit(1);
}
if(!_app.CreateDispatch("Excel.Application", NULL))
{
MessageBox("創(chuàng)建Excel服務失敗！", "信息提示", MB_OK);
return ;
}
//利用模板建立新文檔
_app.SetUserControl(true);
//_app.SetVisible(true);
CFileDialog fileDlg(false);
fileDlg.m_ofn.lpstrFilter="Text Files(*.xls)\0ALL Files(*.*)\0*.*\0\0";
fileDlg.m_ofn.lpstrDefExt="xls";
GetCurrentDirectory(MAX_PATH,path);
CString strPath=path;
CString Path,Name;
strPath+="\\模板";
workBooks=_app.GetWorkbooks();
_workBook=workBooks.Add(_variant_t(strPath));
workSheets=_workBook.GetWorksheets();
_workSheet=workSheets.GetItem(COleVariant((short)1));
range=_workSheet.GetCells();
range.SetItem(_variant_t((long)1), _variant_t((long)3), _variant_t("寫入數(shù)據(jù)了"));
range.SetItem(_variant_t((long)2), _variant_t((long)3), _variant_t("寫入數(shù)據(jù)了"));
range.SetItem(_variant_t((long)3), _variant_t((long)3), _variant_t("寫入數(shù)據(jù)了"));
range.SetItem(_variant_t((long)4), _variant_t((long)3), _variant_t("寫入數(shù)據(jù)了"));
if(IDOK==fileDlg.DoModal())//這里實現(xiàn)“另存為”對話框
{
Path=fileDlg.GetPathName();
Name=fileDlg.GetFileName();
//保存數(shù)據(jù)
_workSheet.SaveAs(Path,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);
///////////////////////////////////////////////////////////////////////////////////////////////////
}
range.ReleaseDispatch();
_workSheet.ReleaseDispatch();
workSheets.ReleaseDispatch();
_workBook.ReleaseDispatch();
workSheets.ReleaseDispatch();
_app.ReleaseDispatch();
_workBook.Close(vtMissing,COleVariant(Path),vtMissing);
workBooks.Close();
_app.Quit();
CoUninitialize();
}
以上幾個問題已經(jīng)解決，倒騰了一晚上。
（1）將char path[MAX_PATH];
GetCurrentDirectory(MAX_PATH,path);
CString strPath=path;定義成全局變量，
（2）workBook.Close(vtMissing,COleVariant(Path),vtMissing);
改成_ COleVariant aver((long)DISP_E_PARAMNOTFOUND, VT_ERROR); workBook.Close(aver,COleVariant(Path),aver);
這樣的話問題就解決了。