Remove first line in Excel file 2

[keepingbusy]

Hi

Sorry guys the search facility is not working.

How can I remove the first row in an Excel spreadsheet created in the following way:

USE MYFILE
STORE SPACE(3) TO mfile
STORE mstart+"-"+mfinish TO mfile
	
EXPORT TO &mfile TYPE XLS
CLOSE DATABASES
	
CLEAR
STORE TRIM(mfile)+".xls" TO mfileopen

DECLARE INTEGER ShellExecute IN shell32.dll ; 
  INTEGER hndWin, ; 
  STRING cAction, ; 
  STRING cFileName, ; 
  STRING cParams, ;  
  STRING cDir, ; 
  INTEGER nShowWin
cFileName = mfileopen
cAction = "open"
ShellExecute(0,cAction,cFileName,"","",1)
CLEAR DLLS ShellExecute

When the Excel file opens, the first row is populated with the column names e.g. A B C D E etc

How can I remove that line?

Using VFP 9.

Thanks guys
Lee

 

[TamarGranor]

Rather than using ShellExecute to open Excel, use Automation and then you can edit the workbook as you want:

oXL = CREATEOBJECT("Excel.Application")
oWorkbook = oXL.Workbooks.Open(mFileOpen)

* Now you can edit oWorkbook
oRange = oWorkbook.Sheets[1].Rows[1]
oRange.Delete()

Untested, but if this isn't right, it should give you the idea.

Tamar

 

[keepingbusy]

Tamar

Before I try your suggestion, can you confirm that this is a way of opening the Excel file to edit the top line and allow the manual removal of it?

If that is the case, then I am already able to do that with my original code. I am looking to create the file and have the first line in the excel spreadsheet deleted automatically before it opens.

Thank you
Lee

 

[Mike Lewis]

Lee,

Why not use a combination of your original code and Tamar's suggestion. In other words, use EXPORT TO to create the file. Then open it via Automation, expunge the top line, and save and close.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Mike

Why not use a combination of your original code and Tamar's suggestion. In other words, use EXPORT TO to create the file. Then open it via Automation, expunge the top line, and save and close.

Perhaps the issue is being confused here.

The original code I posted will open the file and I can highlight Row 1 and delete it manually.

Whilst this isn't a major issue, it would be beneficial if this could be done before the Excel file opens.

Lee

 

[Mike Lewis]

Lee,

The original code I posted will open the file and I can highlight Row 1 and delete it manually.

Whilst this isn't a major issue, it would be beneficial if this could be done before the Excel file opens.

That's exactly what I had in mind.

USE MYFILE
STORE SPACE(3) TO mfile
STORE mstart+"-"+mfinish TO mfile
    
EXPORT TO &mfile TYPE XLS

CLOSE DATABASES
STORE TRIM(mfile)+".xls" TO mfileopen

oXL = CREATEOBJECT("Excel.Application")
oWorkbook = oXL.Workbooks.Open(mFileOpen)

* Now you can edit oWorkbook
oRange = oWorkbook.Sheets[1].Rows[1]
oRange.Delete()

oXL.Visible = .T.

As far as I can see, that will give you the worksheet open on the screen, sans the first line. Isn't that what you want?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Mike

Tried that code and an error appears to the effect that the file name does not exist.

As an example the file name is called a-a.xls

I used a wait mfileopen window command in the code to ensure that mfileopen was infact a-a.xls

The error is in this line:

oWorkbook = oXL.Workbooks.Open(mfileopen)

Lee

 

[TamarGranor]

The code I gave you should open the file, once you create it, remove the first line, and leave it open in Excel.

Mike's code has a typo, using both mfile and mfileopen to refer to the file.

Tamar

 

[Mike Lewis]

Lee,

Does mfileopen include the full path to the file? Even if it's on VFP's search path, you'll need to supply the full path, otherwise Excel won't find it (unless it happens to be in Excel's own default directory).

Tamar,

Mike's code has a typo, using both mfile and mfileopen to refer to the file.

You're not wrong. But I only copied the code from Lee's and your posts.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Mike & Tamar

After some careful consideration, I ran with my original code and did as Tamar suggested, leave the Excel file open, manually delete the first line which contained the column names (A, B, C etc) then clicked on save and exit etc.

This works fine for the time being. I am sure I did read somewhere in this forum once, that there was a way to automatically rather than manually delete the first row of the Excel file.

Thanks again both.

Lee

 

[TamarGranor]

Lee -- the code I showed you should have automatically deleted the first line. I did _not_ suggest you do it manually.

Tamar

 

[keepingbusy]

Hi Tamar

I ran your code and it came up with the same error about the file name so I ignored that just to see what would happen beyond that error and another one came up "Object OWORKBOOK is not found"

I'm grateful for your time but I think I will just run it as I previously mentioned.

Regards
Lee

 

[mm0000]

Keepingbusy,

You need to put the complete path to the Excel file in addition to the name of the Excel file.

mfileopen = 'Drive+path+filename.xls'
oWorkbook = oXL.Workbooks.Open(mfileopen)

 

[Mike Lewis]

Lee,

I'm sorry to labour the point, but the code that Tamar and I gave you works fine. As I mentioned earlier, you only need to make sure that you supply the full path to the XLS file, because Excel doesn't know about VFP's default directory.

I just ran the code, and it worked. The only change I made was to wrap the filename with FULLPATH() in the call to oxl.Workbooks.Open().

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

All

The problem here was I was running the suggestion thinking it would pick up the file name being in the folder the app was running. Obviously this wasn't the case. As this particular app may run in different folders, I've implemented the following:

USE MYFILE
STORE SPACE(3) TO mfile
STORE mstart+"-"+mfinish TO mfile
EXPORT TO &mfile TYPE XLS
[b]STORE SYS(5)+SYS(2003)[/b]+"\"+TRIM(mfile)+".xls" TO mfileopen
oXL = CREATEOBJECT("Excel.Application")
oWorkbook = oXL.Workbooks.Open(mFileOpen)
oRange = oWorkbook.Sheets[1].Rows[1]
oRange.Delete()
oXL.Visible = .T.

That opens the Excel file and deletes the first row.

Issue resolved and may thanks to those who posted

Lee