CSV to XLS
376 views
Skip to first unread message
Robert Cordaiy
Dec 19, 2015, 9:26:38 PM12/19/15
to harbou...@googlegroups.com
11:35 AM (1 hour ago) | |||
Klas Engwall
Dec 20, 2015, 4:21:44 PM12/20/15
to harbou...@googlegroups.com
Hi rob,
> This is the code I amsusing and I get an error message indicating there
> is no export method.
>
> oWorkbook:saveAs(HB_DirBase() + "ItemTypeCount.xls", FileFormatnum=56)
>
> Do I need to use the replace function within the SaveAs function?
Right now I am unable to test anything, because my Excel needs some care
and attention first, but ...
... the Argument:=Value syntax is a VBA peculiarity that does not work
in Harbour. So try to just provide 56 as the second argument.
VBA can use arguments by position or by name. By position works the same
as in Harbour. If by name is used, the arguments can be swapped around,
and you can for example specify the first and the last arguments and
skip the ones inbetween. With positional arguments they must be
specified in the expected order, like we are used to.
The by name syntax is quite confusing for someone with a Clipper/Harbour
background. Here is a page from MSDN that explains it:
https://msdn.microsoft.com/en-us/library/office/gg278645.aspx
The SaveAs method has two different syntaxes - I am not sure how VBA
differentiates between them if the arguments are specified by position,
but as long as only the first few arguments are used, it should not
matter. Here is what the vbaxl9.chm help file says about it (yes, it is
pretty old).
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)
Regards,
Klas
> This is the code I amsusing and I get an error message indicating there
> is no export method.
>
> oWorkbook:saveAs(HB_DirBase() + "ItemTypeCount.xls", FileFormatnum=56)
>
> Do I need to use the replace function within the SaveAs function?
and attention first, but ...
... the Argument:=Value syntax is a VBA peculiarity that does not work
in Harbour. So try to just provide 56 as the second argument.
VBA can use arguments by position or by name. By position works the same
as in Harbour. If by name is used, the arguments can be swapped around,
and you can for example specify the first and the last arguments and
skip the ones inbetween. With positional arguments they must be
specified in the expected order, like we are used to.
The by name syntax is quite confusing for someone with a Clipper/Harbour
background. Here is a page from MSDN that explains it:
https://msdn.microsoft.com/en-us/library/office/gg278645.aspx
The SaveAs method has two different syntaxes - I am not sure how VBA
differentiates between them if the arguments are specified by position,
but as long as only the first few arguments are used, it should not
matter. Here is what the vbaxl9.chm help file says about it (yes, it is
pretty old).
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)
Regards,
Klas
Klas Engwall
Dec 20, 2015, 4:24:20 PM12/20/15
to harbou...@googlegroups.com
Oops, sorry for slipping off the SHIFT key
> Hi rob,
Regards,
Klas
> Hi rob,
Regards,
Klas
Robert Cordaiy
Dec 21, 2015, 5:36:59 PM12/21/15
to harbou...@googlegroups.com
Hi Klas,
No luck as yet. Do you think it would be best to copy the txt to a new workbook and save as normal?
Rob.
--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users
--- You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Klas Engwall
Dec 21, 2015, 7:21:46 PM12/21/15
to harbou...@googlegroups.com
Hi Rob,
> No luck as yet. Do you think it would be best to copy the txt to a new
> workbook and save as normal?
You shoudn't have to, so let's save that option for later.
I am not really sure where you run into problems at this point. Is it
the SaveAs() call itself with its arguments? An invalid numerical value
for the second argument will crash your application. Is that what happens?
I had a chance to test today what I wrote yesterday, and the test worked
like I had expected. I created a worksheet from scratch just like in the
samples that can be found in many previous workgroup discussions. It was
just a small sample, so I did no import. But I could save the workbook
as both .csv and .xls with no problems by changing the second argument.
Ron de Bruin lists all the relevant FileFormatNum numbers in this page:
http://www.rondebruin.nl/win/s5/win001.htm
I used 6 for .csv and -4143 for .xls because I was using an old Excel
version that did not understand FileFormatNum number 56, and -4143 is
the xlWorkbookNormal constant, or the default.
Excel's ability to understand your specific FileFormatNum number is one
thing to be careful about. As you can see in Ron de Bruin's page that I
linked to above, older Excel versions do not understand new
FileFormatNum numbers, just like they do not understand the
corresponding files. And 56 only became the .xls format number in Excel
2007. So to make sure your application will not blow up, leaving Excel
dangling, first check the application version number, and then set the
FileFormatNum number to something that particular Excel version
understands. The Excel Application version numbers can also be found in
that web page.
Assuming that <oExcel> is the name of your OLE object, then you can get
the Excel version with:
nExcelVersion := val( oExcel:Version )
Then you check (at least) if <nExcelVersion> is less than 12 or not, and
choose a valid FileFormatNum number for the resulting file type you
want. I would suggest to start with -4143 and take it from there. Then
you save your workbook using oWorkBook:SaveAs() passing the filename and
the number - just the number, no argument name.
Does that work? If not, can you post a complete and reduced sample of
the code that does not work properly, leaving out everything that is not
absolutely necessary?
Regards,
Klas
> No luck as yet. Do you think it would be best to copy the txt to a new
> workbook and save as normal?
I am not really sure where you run into problems at this point. Is it
the SaveAs() call itself with its arguments? An invalid numerical value
for the second argument will crash your application. Is that what happens?
I had a chance to test today what I wrote yesterday, and the test worked
like I had expected. I created a worksheet from scratch just like in the
samples that can be found in many previous workgroup discussions. It was
just a small sample, so I did no import. But I could save the workbook
as both .csv and .xls with no problems by changing the second argument.
Ron de Bruin lists all the relevant FileFormatNum numbers in this page:
http://www.rondebruin.nl/win/s5/win001.htm
I used 6 for .csv and -4143 for .xls because I was using an old Excel
version that did not understand FileFormatNum number 56, and -4143 is
the xlWorkbookNormal constant, or the default.
Excel's ability to understand your specific FileFormatNum number is one
thing to be careful about. As you can see in Ron de Bruin's page that I
linked to above, older Excel versions do not understand new
FileFormatNum numbers, just like they do not understand the
corresponding files. And 56 only became the .xls format number in Excel
2007. So to make sure your application will not blow up, leaving Excel
dangling, first check the application version number, and then set the
FileFormatNum number to something that particular Excel version
understands. The Excel Application version numbers can also be found in
that web page.
Assuming that <oExcel> is the name of your OLE object, then you can get
the Excel version with:
nExcelVersion := val( oExcel:Version )
Then you check (at least) if <nExcelVersion> is less than 12 or not, and
choose a valid FileFormatNum number for the resulting file type you
want. I would suggest to start with -4143 and take it from there. Then
you save your workbook using oWorkBook:SaveAs() passing the filename and
the number - just the number, no argument name.
Does that work? If not, can you post a complete and reduced sample of
the code that does not work properly, leaving out everything that is not
absolutely necessary?
Regards,
Klas
Robert Cordaiy
Dec 22, 2015, 12:52:26 AM12/22/15
to harbou...@googlegroups.com
Hi Klas,
I don't seem to be able to get the file to save. I am using Excel version 10 which have oExcel:Version of 14. This is the code causing the problem:
oExcel := win_oleCREATEOBJECT( "Excel.Application")
oExcel:Application:DisplayAlerts:=.t.
oWorkBook = oExcel:WorkBooks:OpenText(HB_DirBase() + "ItemTypeCount.CSV")
oSheet := oExcel:ActiveSheet
oSheet:Rows( 1 ):Insert()
oSheet:Rows(1):RowHeight=20
oSheet:Rows(1):Font:Size = 12
oSheet:Range("A1:I2"):Font:FontStyle = "Bold"
oSheet:Cells( 1, 1 ):Value="Number of Item Types by Test "+alltrim(str(year(date())))
oSheet:Range("A1:F1"):MergeCells=1
oSheet:Rows(1):HorizontalAlignment=-4108
oSheet:Rows(2):Range("A1:F1"):Interior:ColorIndex=27
oSheet:Range("A1:F21"):Borders:Color:=0
oSheet:Rows(2):HorizontalAlignment=-4108
oSheet:Rows(8):HorizontalAlignment=-4108
oSheet:Rows(15):HorizontalAlignment=-4108
oSheet:Columns("A:F"):AutoFit()
oWorkBook:saveAs(HB_DirBase()+"ItemTypeCount.xls", 56)
oExcel:Quit()
Thank you for your help with this.
Rob.
Regards,
Klas
Klas Engwall
Dec 22, 2015, 5:39:30 AM12/22/15
to harbou...@googlegroups.com
Hi Rob,
> I don't seem to be able to get the file to save. I am using Excel
> version 10 which have oExcel:Version of 14. This is the code causing the
> problem:
So what is the exact problem? Do you get an RTE and a red box on screen?
Or does it just quit without doing anything you can see? Do you run it
interactively from a cmd.exe window or from a desktop shortcut?
> oExcel := win_oleCREATEOBJECT( "Excel.Application")
> oExcel:Application:DisplayAlerts:=.t.
> oWorkBook = oExcel:WorkBooks:OpenText(HB_DirBase() +
> "ItemTypeCount.CSV")
> oSheet := oExcel:ActiveSheet
> oSheet:Rows( 1 ):Insert()
> oSheet:Rows(1):RowHeight=20
> oSheet:Rows(1):Font:Size = 12
> oSheet:Range("A1:I2"):Font:FontStyle = "Bold"
> oSheet:Cells( 1, 1 ):Value="Number of Item Types by Test
> "+alltrim(str(year(date())))
> oSheet:Range("A1:F1"):MergeCells=1
> oSheet:Rows(1):HorizontalAlignment=-4108
> oSheet:Rows(2):Range("A1:F1"):Interior:ColorIndex=27
> oSheet:Range("A1:F21"):Borders:Color:=0
> oSheet:Rows(2):HorizontalAlignment=-4108
> oSheet:Rows(8):HorizontalAlignment=-4108
> oSheet:Rows(15):HorizontalAlignment=-4108
> oSheet:Columns("A:F"):AutoFit()
> oWorkBook:saveAs(HB_DirBase()+"ItemTypeCount.xls", 56)
> oExcel:Quit()
For the purpose of testing, none of the oSheet rows makes any
difference, so try this version for a moment.
oExcel := win_oleCREATEOBJECT( "Excel.Application")
if oExcel == NIL
? "Excel not available"
wait
endif
oExcel:Visible := .T. // This leaves the workbook open
oExcel:Quit()
Now then, what do you see? Does it make any difference with -4143 for
the format number.
Regards,
Klas
> I don't seem to be able to get the file to save. I am using Excel
> version 10 which have oExcel:Version of 14. This is the code causing the
> problem:
Or does it just quit without doing anything you can see? Do you run it
interactively from a cmd.exe window or from a desktop shortcut?
> oExcel := win_oleCREATEOBJECT( "Excel.Application")
> oExcel:Application:DisplayAlerts:=.t.
> oWorkBook = oExcel:WorkBooks:OpenText(HB_DirBase() +
> "ItemTypeCount.CSV")
> oSheet := oExcel:ActiveSheet
> oSheet:Rows( 1 ):Insert()
> oSheet:Rows(1):RowHeight=20
> oSheet:Rows(1):Font:Size = 12
> oSheet:Range("A1:I2"):Font:FontStyle = "Bold"
> oSheet:Cells( 1, 1 ):Value="Number of Item Types by Test
> "+alltrim(str(year(date())))
> oSheet:Range("A1:F1"):MergeCells=1
> oSheet:Rows(1):HorizontalAlignment=-4108
> oSheet:Rows(2):Range("A1:F1"):Interior:ColorIndex=27
> oSheet:Range("A1:F21"):Borders:Color:=0
> oSheet:Rows(2):HorizontalAlignment=-4108
> oSheet:Rows(8):HorizontalAlignment=-4108
> oSheet:Rows(15):HorizontalAlignment=-4108
> oSheet:Columns("A:F"):AutoFit()
> oWorkBook:saveAs(HB_DirBase()+"ItemTypeCount.xls", 56)
> oExcel:Quit()
difference, so try this version for a moment.
oExcel := win_oleCREATEOBJECT( "Excel.Application")
? "Excel not available"
wait
endif
oExcel:Application:DisplayAlerts:=.t.
oWorkBook = oExcel:WorkBooks:OpenText(HB_DirBase() +;
"ItemTypeCount.CSV")
oWorkBook:SaveAs(HB_DirBase()+"ItemTypeCount.xls", 56)
oWorkBook = oExcel:WorkBooks:OpenText(HB_DirBase() +;
"ItemTypeCount.CSV")
oExcel:Visible := .T. // This leaves the workbook open
oExcel:Quit()
Now then, what do you see? Does it make any difference with -4143 for
the format number.
Regards,
Klas
Robert Cordaiy
Dec 22, 2015, 1:48:17 PM12/22/15
to harbou...@googlegroups.com
Running from the cmd window I get the RTL error (red rectangle) with the msg 'No export method SaveAs'
It does work for an imported DBF file.
Rob.
Robert Cordaiy
Dec 23, 2015, 4:38:23 AM12/23/15
to harbou...@googlegroups.com
Hi KLAS,
The same RTL error message appears and the application stops. Excel remains open and the imported file intact. I could save the file from within Excel as .XLS.
What seems straight forward has become a headache.
Rob
Gale Ford
Dec 23, 2015, 10:32:58 AM12/23/15
to Harbour Users
Maybe oExcel:WorkBooks:OpenText() method does not return the Workbook object you are wanting.
Try
Try
oWorkBook = oExcel:WorkBooks:OpenText(HB_DirBase() + "ItemTypeCount.CSV")
tracelog( valtype( oWorkBook ) )
I would also move
oExcel:Visible := .T.
toward the beginning (right after CreateObject) so you can see what is happening on Excel before your error.
I would also move
oExcel:Visible := .T.
toward the beginning (right after CreateObject) so you can see what is happening on Excel before your error.
Unsubscribe: harbour-users+unsubscribe@googlegroups.com
Web: http://groups.google.com/group/harbour-users
--- You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-users+unsubscribe@googlegroups.com.
Gale Ford
Dec 23, 2015, 11:04:28 AM12/23/15
to Harbour Users
I just verified that :OpenText() method does not return workbook object unlike the :Open() method which does return workbook object.
So you are going to need to assign oWorkBook the ActiveWorkbook object after you use
So you are going to need to assign oWorkBook the ActiveWorkbook object after you use
oExcel:WorkBooks:OpenText(HB_DirBase() + "ItemTypeCount.CSV")
Robert Cordaiy
Dec 24, 2015, 5:45:50 AM12/24/15
to harbou...@googlegroups.com
Hi Gale,
Your suggestion worked, thank you.
The solution was:
oWorkbook:oExcel:ActiveWorkbook()
Rob.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users
--- You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
Robert Cordaiy
Dec 24, 2015, 5:47:26 AM12/24/15
to harbou...@googlegroups.com
Hi Klas,
Thank you god you help with this problem.
The solution was the following bit of code.
oWorkbook:oExcel:ActiveWorkbook()
Rob.
Reply all
Reply to author
Forward
0 new messages