7,763
社区成员
发帖
与我相关
我的任务
分享
Dim Con As ADODB.Connection
Dim sql0 As String
Dim strtemp As String
Dim My_temp As New ADODB.Recordset
Dim Name() As String
strtemp = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyTest;Data Source=珞珈风云1-PC\SQLEXPRESS;"
' Con.Open strtemp
' s = OName(Name()) '部分数据
' sql0 = "SELECT [日期] ," & "[" & TextColums.Text & "]" & " from sysobjects Where Name like " & " '%" & Name(4, 1) & "%'" & " and [日期] > '" & TextTB.Text & "' and [日期] < '" & TextTE.Text & "'"
sql0 = "declare @searchTables nvarchar(100) set @searchTables = '%SH000001" _
& "' declare @sqlCommand nvarchar(4000) set @sqlCommand = '' " _
& "declare @dateB varchar(10) set @dateB = " & TextTB.Text _
& "declare @dateE varchar(100) set @dateE = " & TextTE.Text _
& ";with tableNames as ( select all 'select [日期],[成交额] from [MyTest].[dbo].[' + Name + '] where [日期] >= ''' + @dateB + ''' and [日期] <= ''' + @dateE + '''' as SelectTable from sys.tables where name like @searchTables) " _
& " select @sqlCommand = @sqlCommand + SelectTable from tableNames exec (@sqlCommand)"
My_temp.Open sql0, strtemp, adOpenKeyset, adLockOptimistic
Set mExcelFile = CreateObject("Excel.Sheet")
mExcelFile.Application.Range("A1").CopyFromRecordset My_temp
mExcelFile.SaveAs "G:\123.xls"
Set mExcelFile = Nothing
My_temp.Close
cells(3,1).address
mExcelFile.Application.Range("A1").CopyFromRecordset My_temp.NextRecordset(RecordAffected)
复制的方法给excel赋值,但是没有使用cell的参数的函数,我怎样能将cell换为A1的形式Cells((N - 1 ) * 2 + 1 , 1)
所以在查询完毕以后,可以用
Dim FirstCell As Range
Set FirstCell = Cells((tblNo - 1) * 2 +1, 1)
来确定你的第N张表的起始位置并从此处开始粘贴表
查询过程写得过于复杂了
非常想吐槽你的EXCEL表结构,横向排列的话,数据处理会很痛苦,比如做数据透视,建议竖向导入数据,后续处理会比较轻松。