sdwd_lhq 发表于 2017-3-27 21:26:07

au3中如何在已经打开的excel中引入外部的CSV文件?

au3中如何在已经打开的excel中引入外部的CSV文件?
下面的代码是先创建excel对象,然后设置单元个的格式为文本,因为里面有的列是长的数码文本列,ActiveSheet.QueryTables.Add执行没有成功,不知au3如何书写?
      $filename = "c:\lhq.csv"
        $oExcel = ObjCreate("Excel.Application")
        $oExcel.Visible = True
        $oExcel.DisplayAlerts = True
        $oWorkbook_o = $oExcel.Workbooks.Add
        $oExcel.Columns.Select()
        $oExcel.Selection.NumberFormatLocal = "@"
      $oExcel.ActiveSheet.QueryTables.Add('Connection:= "TEXT;' & $filename & '"', 'Destination:=Range("A1")')
通过excel的宏录制引入文本文件如下:
Sub Macro1()
'
' Macro1 Macro
' 宏由 Administrator 录制，时间: 2017-3-27
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
      "TEXT;C:\Documents and Settings\Administrator\桌面\lhq.csv", Destination:=Range _
      ("A1"))
      .Name = "lhq"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      .TextFilePlatform = 936
      .TextFileStartRow = 1
      .TextFileParseType = xlDelimited
      .TextFileTextQualifier = xlTextQualifierDoubleQuote
      .TextFileConsecutiveDelimiter = False
      .TextFileTabDelimiter = False
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = True
      .TextFileSpaceDelimiter = False
      .TextFileColumnDataTypes = Array(1, 1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
      .TextFileTrailingMinusNumbers = True
      .Refresh BackgroundQuery:=False
    End With

End Sub

h20040606 发表于 2017-3-28 09:35:40

假设将文件保存到c:\mytest.xlsx中去
$filename = "c:\lhq.csv"
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = True
$oExcel.DisplayAlerts = True
$oWorkbook_o = $oExcel.Workbooks.Add
$oWorkbook_o.activesheet.Columns.Select
$oWorkbook_o.activesheet.Selection.NumberFormatLocal = "@"
Local $aArray=
With $oWorkbook_o.ActiveSheet.QueryTables.Add("TEXT;" &$filename , $oWorkbook_o.activesheet.Range("A1"))
      .Name = "lhq"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .RefreshStyle = 1; xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      .TextFilePlatform = 936
      .TextFileStartRow = 1
      .TextFileParseType = 1;xlDelimited
      .TextFileTextQualifier = 1 ;xlTextQualifierDoubleQuote
      .TextFileConsecutiveDelimiter = False
      .TextFileTabDelimiter = False
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = True
      .TextFileSpaceDelimiter = False
      .TextFileColumnDataTypes =$aArray; Array(1, 1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
      .TextFileTrailingMinusNumbers = True
      .Refresh(False)
EndWith
$oWorkbook_o.saveas("c:\mytest.xls",51)
$oWorkbook_o.close
$oExcel.quit

jingzhinvr 发表于 2017-4-2 02:03:35

这个是什么代码
页: [1]
查看完整版本: au3中如何在已经打开的excel中引入外部的CSV文件?