📄 vb如何写sql server 2000数据导出到excel？.htm
字号:
google_ad_height = 250;
google_ad_format = "250x250_as";
google_ad_type = "text_image";
google_ad_channel = "7174608304";
//google_myset
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script> </td>
</tr>
</table>
<p>提问：</p><hr style='height:1px;color:#C1CDD8'>
<p>问者:imur2008() 信誉:90 级别:user1 日期:2006-8-29 0:30:44</p>
<pre style='word-wrap: break-word;height:50px;overflow-x:visible;overflow-y:visible;'>请教！！！</pre><hr style='height:1px;color:#C1CDD8'>
<p>回复： </p><hr style='height:1px;color:#C1CDD8'>
<hr style='height:1px;color:#C1CDD8'>
<p>答者:fxy_2002(阿勇) 信誉:100 级别:user4 日期:2006-8-29 8:39:40 id:36415088</p>
<pre style='word-wrap: break-word;height:50px;overflow-x:auto;overflow-y:visible;'>http://www.microsoft.com/china/community/Column/32.mspx[转]Excel 是一个非常优秀的报表制作软件，用VBA可以控制其生成优秀的报表，本文通过添加查询语句的方法，即用Excel中的获取外部数据的功能将数据很快地从一个查询语句中捕获到EXCEL中，比起往每个CELL里写数据的方法提高许多倍。在程序中引用Microsoft Excel 9.0 Object Library，将下文加入到一个模块中,窗体中调用如下ExporToExcel("select * from table")。则实现快速将数据导出到EXCEL中。Public Function ExporToExcel(strOpen As String)'*********************************************************'* 名称：ExporToExcel'* 功能：导出数据到EXCEL'* 用法：ExporToExcel(sql查询字符串)'*********************************************************Dim Rs_Data As New ADODB.RecordsetDim Irowcount As IntegerDim Icolcount As Integer Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlQuery As Excel.QueryTable With Rs_Data If .State = adStateOpen Then .Close End If .ActiveConnection = Cn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Source = strOpen .Open End With With Rs_Data If .RecordCount < 1 Then MsgBox ("没有记录!") Exit Function End If '记录总数 Irowcount = .RecordCount '字段总数 Icolcount = .Fields.Count End With Set xlApp = CreateObject("Excel.Application") Set xlBook = Nothing Set xlSheet = Nothing Set xlBook = xlApp.Workbooks().Add Set xlSheet = xlBook.Worksheets("sheet1") xlApp.Visible = True '添加查询语句，导入EXCEL数据 Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1")) With xlQuery .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True End With xlQuery.FieldNames = True '显示字段名 xlQuery.Refresh With xlSheet .Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Name = "黑体" '设标题为黑体字 .Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Bold = True '标题字体加粗 .Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous '设表格边框样式 End With With xlSheet.PageSetup .LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称：" ' & Gsmc .CenterHeader = "&""楷体_GB2312,常规""公司人员情况表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期：" .RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位：" .LeftFooter = "&""楷体_GB2312,常规""&10制表人：" .CenterFooter = "&""楷体_GB2312,常规""&10制表日期：" .RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页" End With xlApp.Application.Visible = True Set xlApp = Nothing '"交还控制给Excel Set xlBook = Nothing Set xlSheet = NothingEnd Function注:：在程序中引用'Microsoft Excel 9.0 Object Library'和ADO对象,机器必装Excel 2000本程序在Windows 98/2000,VB 6 下运行通过。**********************************************调用那个函数，传递的查询语句每次查一个范围，比如... where id between 1 and 65000...... where id between 65001 and 130000 ...循环处理即可。</pre>
<hr style='height:1px;color:#C1CDD8'>
<p>答者:zq972(最近不想写代码，好累~~~~) 信誉:100 级别:user1 日期:2006-8-29 8:58:10 id:36415586</p>
<pre style='word-wrap: break-word;height:50px;overflow-x:auto;overflow-y:visible;'>excel 最大行数限制为 65,534</pre>
<hr style='height:1px;color:#C1CDD8'>
<p>答者:sunmoon2222() 信誉:100 级别:user1 日期:2006-10-19 20:53:19 id:37329962</p>
<pre style='word-wrap: break-word;height:50px;overflow-x:auto;overflow-y:visible;'>mark</pre></td></tr></table><script type="text/javascript"><!--
google_language = "zh-CN";
google_alternate_ad_url = "http://access911.net/index.asp";
google_ad_client = "pub-6733870115125334";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
//google_color_border = "336699";
google_color_border = "FFFFFF";
google_color_bg = "FFFFFF";
google_color_link = "0000FF";
google_color_text = "000000";
google_color_url = "008000";
google_ad_type = "text_image";
//2007-01-11: 每个CSDN文章页脚广告
google_ad_channel = "6443167733";
//google_myset
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script> <style type="text/css">
<!--
.input1
{
BACKGROUND-COLOR: #ffffff;
BORDER-BOTTOM: #666666 1px solid;
BORDER-LEFT: #666666 1px solid;
BORDER-RIGHT: #666666 1px solid;
BORDER-TOP: #666666 1px solid;
COLOR: #666666;
HEIGHT: 18px;
border-color: #666666 #666666 #666666 #666666; font-size: 9pt
}
-->
</style>
<!-- SiteSearch Google -->
<form method="get" action="http://www.google.cn/custom" target="_top">
<table border="0" bgcolor="#ffffff">
<tr><td nowrap="nowrap" valign="top" align="left" height="32">
<a href="http://www.google.com/">
<img src="http://www.google.com/logos/Logo_25wht.gif" border="0" alt="Google" align="middle"></img></a>
</td>
<td nowrap="nowrap">
<input type="hidden" name="domains" value="access911.net;e.access911.net;bbs.access911.net"></input>
<label for="sbi" style="display: none">输入您的搜索字词</label>
<input type="text" name="q" size="50" maxlength="255" value="" id="sbi" class="input1"></input>
<label for="sbb" style="display: none">提交搜索表单</label>
<input type="submit" name="sa" value="搜索" id="sbb" class="input1"></input>
</td></tr>
<tr>
<td> </td>
<td nowrap="nowrap">
<table>
<tr>
<td>
<input type="radio" name="sitesearch" value="" checked id="ss0"></input>
<label for="ss0" title="搜索网络"><font size="-1" color="#000000">Web</font></label></td>
<td>
<input type="radio" name="sitesearch" value="access911.net" id="ss1"></input>
<label for="ss1" title="搜索 access911.net"><font size="-1" color="#000000">access911.net</font></label></td>
</tr>
<tr>
<td>
<input type="radio" name="sitesearch" value="e.access911.net" id="ss2"></input>
<label for="ss2" title="搜索 e.access911.net"><font size="-1" color="#000000">e.access911.net</font></label></td>
<td>
<input type="radio" name="sitesearch" value="bbs.access911.net" id="ss3"></input>
<label for="ss3" title="搜索 bbs.access911.net"><font size="-1" color="#000000">bbs.access911.net</font></label></td>
</tr>
</table>
<input type="hidden" name="client" value="pub-6733870115125334"></input>
<input type="hidden" name="forid" value="1"></input>
<input type="hidden" name="ie" value="GB2312"></input>
<input type="hidden" name="oe" value="GB2312"></input>
<input type="hidden" name="flav" value="0000"></input>
<input type="hidden" name="sig" value="IEdOP4qWweh99NCf"></input>
<input type="hidden" name="cof" value="GALT:#008000;GL:1;DIV:#336699;VLC:663399;AH:center;BGC:FFFFFF;LBGC:336699;ALC:0000FF;LC:0000FF;T:000000;GFNT:0000FF;GIMP:0000FF;FORID:1"></input>
<input type="hidden" name="hl" value="zh-CN"></input>
</td></tr></table>
</form>
<!-- SiteSearch Google --> </td> </tr> </table> <p align="center"> </p> </td> </tr> </table> <img height="1" src="/template/MSNstyle/spacer.gif" width="502"></td> <td width="160" rowspan="4" valign="top" bgcolor="#dbeaf5"></td> </tr> <tr> <td valign="top" bgcolor="#dbeaf5"> </td> </tr> <tr> <td valign="top" bgcolor="#dbeaf5"> </td> </tr> <tr> <td valign="bottom" bgcolor="#dbeaf5"> </td> </tr></table><script language="javascript">var frm = document.hotmail;folderID = "F000000001";MessengerCell = "4";ColspanSize = "5";</script><table cellspacing="0" cellpadding="0" width="100%" border="0"> <tr> <td colspan="2" height="24"> </td> </tr> <tr> <td height="24"> <table cellspacing="0" cellpadding="0" border="0"> <tr> <td nowrap> <a class="F" href="http://access911.net">Access911.net</a> </td> <td><font class="G">|</font></td> <td nowrap> <a class="F" href="http://access911.net/bbs">a9BBS</a> </td> <td><font class="G">|</font></td> <td nowrap> <a class="F" href="#">OTaA System</a> </td> <td><font class="G">|</font></td> </tr> </table> </td> <td nowrap align="right"><font class="G">建站日期：<a href="/HST.htm" title="欲了解本站历史请单击这里" style="color:white">2000年4月2日</a><a href="/fixhtm/indexnew.html" title="静态资料索引页 专为搜索引擎准备的不可见链接"></a> | 设计施工：陈格 ( access911 & cg1 ) </font></td> </tr> <tr> <td nowrap colspan="2" height="20"> <font class="G">Copyright ? 2000 - 2003 COMET, 陈格 保留所有权利</font></td> </tr> <tr> <td nowrap colspan="2" height="20" align="center"><script language="javascript" type="text/javascript" src="http://js.users.51.la/597881.js"></script>
<noscript><a href="http://www.51.la/?597881" target="_blank"><img alt="我要啦免费统计" src="http://img.users.51.la/597881.asp" style="border:none" /></a></noscript>
<script language="javascript" type="text/javascript" src="http://js.users.51.la/659499.js"></script>
<noscript><a href="http://www.51.la/?659499" target="_blank"><img alt="我要啦免费统计" src="http://img.users.51.la/659499.asp" style="border:none" /></a></noscript>
<script src='http://s74.cnzz.com/stat.php?id=279235&web_id=279235&show=pic' language='JavaScript' charset='gb2312'></script>
<script language="javascript" src="http://count29.51yes.com/click.aspx?id=298380587&logo=12"></script>
<!-- 后面是客服代码 --> </td> </tr> <tr> <td colspan="2"> <img height="1" src="/template/MSNstyle/spacer.gif" width="779"></td> </tr></table><img height="1" src="/template/MSNstyle/c.gif" width="1"><script type="text/javascript" src="http://access911.net/tj911.asp?siteid=1&style=1"></script><script language='javascript' type='text/javascript'><!-- if (top.location !== self.location){ top.location = self.location;} --></script></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -