Hi all,

I have a problem importing data from csv file into excel. Through the 'open' 
command it works fine, but I want to create an excel workbook with several 
sheets and import different data from several csv-files into this workbook.
The VBA command is:  Activesheet.QueryTables.Add(Connection 
:="TEXT;c:\temp\test.csv", Destination := .Range("A1"))

I've tried a number of sytaxis, but it doesn't seem to be correct.
for example:
oSheet:Querytables.callMethod("Add","TEXT;c:\temp\test.csv", 
oSheet:Range("A1"))
oSheet:Querytables:Add(connection := "TEXT;c:\temp\test.csv", destination := 
oSheet:Range("A1"))

Can anyone help please......

greetz,
Marty

Marty,

> I have a problem importing data from csv file into excel. Through the 'open' 
> command it works fine, but I want to create an excel workbook with several 
> sheets and import different data from several csv-files into this workbook.
> The VBA command is:  Activesheet.QueryTables.Add(Connection 
> :="TEXT;c:\temp\test.csv", Destination := .Range("A1"))
> 
> I've tried a number of sytaxis, but it doesn't seem to be correct.
> for example:
> oSheet:Querytables.callMethod("Add","TEXT;c:\temp\test.csv", 
> oSheet:Range("A1"))
> oSheet:Querytables:Add(connection := "TEXT;c:\temp\test.csv", destination := 
> oSheet:Range("A1"))
> 
> Can anyone help please......

I see all your posts are the same, so I take the first one...
I am not terribly familiar using Excel via ActiveX. There are quite some
posts on this issue, so perhaps searching this subgroup might give you some
answers.
Your Office installation comes with quite extensive help files on the Excel
object (on my system: VBAXL10.CHM), that might also give you some
information.
In this guide I see that range can take two values and perhaps there might
be (part of) the solution for your problem. Although there is no actual
description of your problem, so I assumed it just does not work, that is
you don't see any results.

Peter

I haven't worked with direct imported from a CSV, but I do create Excel 
spreadsheets from multi-dimensional arrays.

You could convert the CSV files to sub-arrays, then put all the sub-arrays 
into a single array and then use the below function:

FUNCTION DC_Array2Excel( cExcelFile, aData, nOrientation, lDisplayAlerts, ;
                         lVisible, lAutoFit, lTrimNilColumns, 
lCombineSheets )

LOCAL oExcel, oBook, oSheet, i, j, oScrn, xData, nSheet, xValue, lStatus := 
.t., ;
      oProgress1, oProgress2, GetList[0], GetOptions, oDlg, nRowOffset, 
nLastRow, ;
      nColumns, nRows, cRange, oCells, cColId, nStartRow, bError

DEFAULT nOrientation := xlLandscape, ;
        lDisplayAlerts := .f., ;
        lVisible := .f., ;
        lAutoFit := .t., ;
        cExcelFile := DC_Path(AppName(.t.)) + 'worksheet.xls', ;
        lTrimNilColumns := .f., ;
        lCombineSheets := .f.

#if XPPVER > 1900000
   Create the "Excel.Application" object
  oExcel := CreateObject("Excel.Application")
  IF Empty( oExcel )
    DC_WinAlert( "Excel is not installed" )
    RETURN .f.
  ENDIF
#else
  DC_WinAlert('This feature is available in Xbase++ 1.9 and later only!')
  RETURN .f.
#endif

IF lTrimNilColumns
  FOR i := 1 TO Len(aData)
    aData[i] := DC_ArrayTrim(aData[i])
  NEXT
ENDIF

 Avoid message boxes such as "File already exists". Also,
 ensure the Excel application is visible.
oExcel:DisplayAlerts := lDisplayAlerts
oExcel:visible       := lVisible

 Add a workbook to the Excel application. Query for
 the active sheet (sheet-1) and set up page/paper
 orientation.

@ 0,0 DCSAY 'Creating Excel Worksheet: ' + cExcelFile SAYSIZE 0

@ 1,0 DCPROGRESS oProgress1 SIZE 50,1 ;
      TYPE XBPSTATIC_TYPE_TEXT ;
      COLOR GRA_CLR_CYAN, GRA_CLR_WHITE ;
      PERCENT ;
      PERCENTCOLOR GRA_CLR_RED ;
      RADIUS 20 ;
      OUTLINE ;
      DYNAMIC

@ 3,0 DCPUSHBUTTON CAPTION 'Cancel' SIZE 9,1.2 ACTION {||lStatus:=.f.}

DCGETOPTIONS ;
   NORESIZE ;
   ALWAYSONTOP ;
   _PIXEL .f.

DCREAD GUI FIT TITLE 'Exporting to Excel' ;
   MODAL EXIT PARENT @oDlg OPTIONS GetOptions NOAUTORESTORE

oBook  := oExcel:workbooks:Add()

nRowOffset := 0
nLastRow := 0

nStartRow := 1

FOR nSheet := 1 TO Len(aData)

  IF Empty(aData[nSheet])
    LOOP
  ENDIF

  IF !lStatus
    EXIT
  ENDIF
  DC_GetProgress(oProgress1,nSheet,Len(aData))

  IF nSheet > oBook:Sheets:Count
    IF !lCombineSheets
      oSheet := oBook:Sheets:Add()
      nStartRow := 1
    ELSE
      oSheet := oBook:Sheets:Item(1)
    ENDIF
  ELSEIF lCombineSheets
    oSheet := oBook:Sheets:Item(1)
  ELSE
    oSheet := oBook:Sheets:Item(nSheet)
    nStartRow := 1
  ENDIF

  oSheet:PageSetup:Orientation := nOrientation

   Feed in the data from the table to the Cells
   of the sheet.

  nColumns := Len(aData[nSheet,1])
  nRows := Len(aData[nSheet])
  cRange := 'A' + Alltrim(Str(nStartRow)) + ':' + 
Get_Excel_Column_ID(nColumns) + LTrim(Str(nRows+nStartRow))

  FOR i := 1 TO nRows
    FOR j := 1 TO nColumns
      aData[nSheet,i,j] := Alltrim(DC_XtoC(aData[nSheet,i,j]))
      aData[nSheet,i,j] := Strtran(aData[nSheet,i,j],'=','')
      aData[nSheet,i,j] := Strtran(aData[nSheet,i,j],'-','')
    NEXT
  NEXT

  oSheet:Range(cRange):Value := aData[nSheet]

   Force a reformat for the size of the first column
  IF lAutoFit
    FOR i := 1 TO Len(aData[nSheet,1])
      oSheet:Columns(i):AutoFit()
    NEXT
  ENDIF

   Force a reformat for the size of the first column
  IF lAutoFit
    FOR i := 1 TO Len(aData[nSheet,1])
      oSheet:Columns(i):AutoFit()
    NEXT
  ENDIF

  nStartRow += Len(aData[nSheet])

NEXT

IF Empty(DC_Path(cExcelFile))
  cExcelFile := DC_Path(AppName(.t.)) + cExcelFile
ENDIF

cExcelFile := Strtran(cExcelFile,'\\','\')

bError := ErrorBlock({|e|Break(e)})
BEGIN SEQUENCE

oDlg:Destroy()

 Save workbook as ordinary excel file.
oBook:SaveAs(cExcelFile,xlWorkbookNormal)

END SEQUENCE
ErrorBlock(bError)

oBook:close()
oBook:destroy()

 Quit Excel
oExcel:Quit()
oExcel:Destroy()

RETURN .t.

* ---------------

FUNCTION Get_Excel_Column_ID( i )

LOCAL cAlpha := "ABCDEFGHIJKLMNOPQRSTUVWXYZ", cLastRow := ""

IF i > 26
  cLastRow := Substr(cAlpha,Int(i/26),1) + Substr(cAlpha,Mod(i,26),1)
ELSE
  cLastRow := Substr(cAlpha,i,1)
ENDIF

RETURN cLastRow

* ---------------

FUNCTION DC_ArrayTrim( aData )

LOCAL nColumns, i, j, lEmpty

IF Empty(aData)
  RETURN aData
ENDIF

FOR i := 1 TO Len(aData)
  IF aData[i] == NIL
    ARemove(aData,i)
    i--
  ENDIF
NEXT

IF Valtype(aData[1]) == 'A'
  nColumns := Len(aData[1])
ELSE
  RETURN aData
ENDIF

FOR i := 1 TO nColumns
  lEmpty := .t.
  FOR j := 1 TO Len(aData)
    IF !aData[j,i] == NIL
      lEmpty := .f.
      EXIT
    ENDIF
  NEXT
  IF lEmpty
    FOR j := 1 TO Len(aData)
      ARemove( aData[j], i)
    NEXT
    nColumns--
    i := 0
  ENDIF
NEXT

RETURN aData


"Marty" <depost@checkjemail.nl> wrote in message 
news:9daf7af$4334e812$ace@news.alaska-software.com...
> Hi all,
>
> I have a problem importing data from csv file into excel. Through the 
> 'open' command it works fine, but I want to create an excel workbook with 
> several sheets and import different data from several csv-files into this 
> workbook.
> The VBA command is:  Activesheet.QueryTables.Add(Connection 
> :="TEXT;c:\temp\test.csv", Destination := .Range("A1"))
>
> I've tried a number of sytaxis, but it doesn't seem to be correct.
> for example:
> oSheet:Querytables.callMethod("Add","TEXT;c:\temp\test.csv", 
> oSheet:Range("A1"))
> oSheet:Querytables:Add(connection := "TEXT;c:\temp\test.csv", destination 
> := oSheet:Range("A1"))
>
> Can anyone help please......
>
> greetz,
> Marty

Thank you for your suggestion, however I am not familiar with your DC 
functioncalls.
I myself created a workaround in which I open the csv file, copy the 
contents and paste
it in the overall spreadsheet then delete the csv file. For example:

  oExcel := CreateObject("Excel.Application")
  IF Empty( oExcel )
    MsgBox( "Excel is not installed" )
    RETURN nil
  ENDIF

oBook := oExcel:Workbooks:Add()

cCSV:= "C:\temp\test1.csv"
oSheet := oBook:Sheets:Add()
oSheet:Name := "test1"

oCSV := oExcel:Workbooks:Open(cCSV)

oCSV:Activesheet:Cells:Copy
oSheet:Paste
oCSV:Close
DELETE FILE (cCSV)

cCSV:= "C:\temp\test2.csv"
oSheet := oBook:Sheets:Add()
oSheet:Name := "test2"

oCSV := oExcel:Workbooks:Open(cCSV)

oCSV:Activesheet:Cells:Copy
oSheet:Paste
oCSV:Close
DELETE FILE (cCSV)

oBook:SaveAs("c:\temp\Overall.xls",xlWorkbookNormal)

oExcel:Quit()
oExcel:Destroy()

This works fine, but the I still would like to know the syntaxis for the 
QueryTables command...


"Roger Donnay" <rogerdonnay@donnay-software.com> schreef in bericht 
news:139e0cfd$4869bcd5$b83@news.alaska-software.com...
>I haven't worked with direct imported from a CSV, but I do create Excel 
>spreadsheets from multi-dimensional arrays.
>
> You could convert the CSV files to sub-arrays, then put all the sub-arrays 
> into a single array and then use the below function:
>
> FUNCTION DC_Array2Excel( cExcelFile, aData, nOrientation, lDisplayAlerts, 
> ;
>                         lVisible, lAutoFit, lTrimNilColumns, 
> lCombineSheets )
>
> LOCAL oExcel, oBook, oSheet, i, j, oScrn, xData, nSheet, xValue, lStatus 
> := .t., ;
>      oProgress1, oProgress2, GetList[0], GetOptions, oDlg, nRowOffset, 
> nLastRow, ;
>      nColumns, nRows, cRange, oCells, cColId, nStartRow, bError
>
> DEFAULT nOrientation := xlLandscape, ;
>        lDisplayAlerts := .f., ;
>        lVisible := .f., ;
>        lAutoFit := .t., ;
>        cExcelFile := DC_Path(AppName(.t.)) + 'worksheet.xls', ;
>        lTrimNilColumns := .f., ;
>        lCombineSheets := .f.
>
> #if XPPVER > 1900000
>   Create the "Excel.Application" object
>  oExcel := CreateObject("Excel.Application")
>  IF Empty( oExcel )
>    DC_WinAlert( "Excel is not installed" )
>    RETURN .f.
>  ENDIF
> #else
>  DC_WinAlert('This feature is available in Xbase++ 1.9 and later only!')
>  RETURN .f.
> #endif
>
> IF lTrimNilColumns
>  FOR i := 1 TO Len(aData)
>    aData[i] := DC_ArrayTrim(aData[i])
>  NEXT
> ENDIF
>
>  Avoid message boxes such as "File already exists". Also,
>  ensure the Excel application is visible.
> oExcel:DisplayAlerts := lDisplayAlerts
> oExcel:visible       := lVisible
>
>  Add a workbook to the Excel application. Query for
>  the active sheet (sheet-1) and set up page/paper
>  orientation.
>
> @ 0,0 DCSAY 'Creating Excel Worksheet: ' + cExcelFile SAYSIZE 0
>
> @ 1,0 DCPROGRESS oProgress1 SIZE 50,1 ;
>      TYPE XBPSTATIC_TYPE_TEXT ;
>      COLOR GRA_CLR_CYAN, GRA_CLR_WHITE ;
>      PERCENT ;
>      PERCENTCOLOR GRA_CLR_RED ;
>      RADIUS 20 ;
>      OUTLINE ;
>      DYNAMIC
>
> @ 3,0 DCPUSHBUTTON CAPTION 'Cancel' SIZE 9,1.2 ACTION {||lStatus:=.f.}
>
> DCGETOPTIONS ;
>   NORESIZE ;
>   ALWAYSONTOP ;
>   _PIXEL .f.
>
> DCREAD GUI FIT TITLE 'Exporting to Excel' ;
>   MODAL EXIT PARENT @oDlg OPTIONS GetOptions NOAUTORESTORE
>
> oBook  := oExcel:workbooks:Add()
>
> nRowOffset := 0
> nLastRow := 0
>
> nStartRow := 1
>
> FOR nSheet := 1 TO Len(aData)
>
>  IF Empty(aData[nSheet])
>    LOOP
>  ENDIF
>
>  IF !lStatus
>    EXIT
>  ENDIF
>  DC_GetProgress(oProgress1,nSheet,Len(aData))
>
>  IF nSheet > oBook:Sheets:Count
>    IF !lCombineSheets
>      oSheet := oBook:Sheets:Add()
>      nStartRow := 1
>    ELSE
>      oSheet := oBook:Sheets:Item(1)
>    ENDIF
>  ELSEIF lCombineSheets
>    oSheet := oBook:Sheets:Item(1)
>  ELSE
>    oSheet := oBook:Sheets:Item(nSheet)
>    nStartRow := 1
>  ENDIF
>
>  oSheet:PageSetup:Orientation := nOrientation
>
>   Feed in the data from the table to the Cells
>   of the sheet.
>
>  nColumns := Len(aData[nSheet,1])
>  nRows := Len(aData[nSheet])
>  cRange := 'A' + Alltrim(Str(nStartRow)) + ':' + 
> Get_Excel_Column_ID(nColumns) + LTrim(Str(nRows+nStartRow))
>
>  FOR i := 1 TO nRows
>    FOR j := 1 TO nColumns
>      aData[nSheet,i,j] := Alltrim(DC_XtoC(aData[nSheet,i,j]))
>      aData[nSheet,i,j] := Strtran(aData[nSheet,i,j],'=','')
>       aData[nSheet,i,j] := Strtran(aData[nSheet,i,j],'-','')
>    NEXT
>  NEXT
>
>  oSheet:Range(cRange):Value := aData[nSheet]
>
>   Force a reformat for the size of the first column
>  IF lAutoFit
>    FOR i := 1 TO Len(aData[nSheet,1])
>      oSheet:Columns(i):AutoFit()
>    NEXT
>  ENDIF
>
>   Force a reformat for the size of the first column
>  IF lAutoFit
>    FOR i := 1 TO Len(aData[nSheet,1])
>      oSheet:Columns(i):AutoFit()
>    NEXT
>  ENDIF
>
>  nStartRow += Len(aData[nSheet])
>
> NEXT
>
> IF Empty(DC_Path(cExcelFile))
>  cExcelFile := DC_Path(AppName(.t.)) + cExcelFile
> ENDIF
>
> cExcelFile := Strtran(cExcelFile,'\\','\')
>
> bError := ErrorBlock({|e|Break(e)})
> BEGIN SEQUENCE
>
> oDlg:Destroy()
>
>  Save workbook as ordinary excel file.
> oBook:SaveAs(cExcelFile,xlWorkbookNormal)
>
> END SEQUENCE
> ErrorBlock(bError)
>
> oBook:close()
> oBook:destroy()
>
>  Quit Excel
> oExcel:Quit()
> oExcel:Destroy()
>
> RETURN .t.
>
> * ---------------
>
> FUNCTION Get_Excel_Column_ID( i )
>
> LOCAL cAlpha := "ABCDEFGHIJKLMNOPQRSTUVWXYZ", cLastRow := ""
>
> IF i > 26
>  cLastRow := Substr(cAlpha,Int(i/26),1) + Substr(cAlpha,Mod(i,26),1)
> ELSE
>  cLastRow := Substr(cAlpha,i,1)
> ENDIF
>
> RETURN cLastRow
>
> * ---------------
>
> FUNCTION DC_ArrayTrim( aData )
>
> LOCAL nColumns, i, j, lEmpty
>
> IF Empty(aData)
>  RETURN aData
> ENDIF
>
> FOR i := 1 TO Len(aData)
>  IF aData[i] == NIL
>    ARemove(aData,i)
>    i--
>  ENDIF
> NEXT
>
> IF Valtype(aData[1]) == 'A'
>  nColumns := Len(aData[1])
> ELSE
>  RETURN aData
> ENDIF
>
> FOR i := 1 TO nColumns
>  lEmpty := .t.
>  FOR j := 1 TO Len(aData)
>    IF !aData[j,i] == NIL
>      lEmpty := .f.
>      EXIT
>    ENDIF
>  NEXT
>  IF lEmpty
>    FOR j := 1 TO Len(aData)
>      ARemove( aData[j], i)
>    NEXT
>    nColumns--
>    i := 0
>  ENDIF
> NEXT
>
> RETURN aData
>
>
> "Marty" <depost@checkjemail.nl> wrote in message 
> news:9daf7af$4334e812$ace@news.alaska-software.com...
>> Hi all,
>>
>> I have a problem importing data from csv file into excel. Through the 
>> 'open' command it works fine, but I want to create an excel workbook with 
>> several sheets and import different data from several csv-files into this 
>> workbook.
>> The VBA command is:  Activesheet.QueryTables.Add(Connection 
>> :="TEXT;c:\temp\test.csv", Destination := .Range("A1"))
>>
>> I've tried a number of sytaxis, but it doesn't seem to be correct.
>> for example:
>> oSheet:Querytables.callMethod("Add","TEXT;c:\temp\test.csv", 
>> oSheet:Range("A1"))
>> oSheet:Querytables:Add(connection := "TEXT;c:\temp\test.csv", destination 
>> := oSheet:Range("A1"))
>>
>> Can anyone help please......
>>
>> greetz,
>> Marty
>
>