[RESOLVED] Excel automation from VB6 - how to load pictures and docs.

[camoore]

Am using Excel automation from VB6. code based on Si_the_Geek's excellent tutorial. So far so good.

The desire is now on sheets 2 and 3 of a created Excel file to introduce a picture (abc.jpg) and a document (def.txt) under automation from VB6.

Suggestions / pointers would be much appreciated please.

camoore

Wales, UK

[Siddharth Rout]

1) Inserting the pics

oSheet.Pictures.Insert ("C:\Temp\MyPic.jpg")

2) What kind of text file is it? Can you attach a sample of the text file?

[camoore]

1. Thank you koolsid for such a quick reply. That method for inserting a picture worked fine, but the code I needed was

oXLSheet.Pictures.Insert ("C:\Temp\MyPic.jpg")

ie. I had to add the "XL" and then it was perfect.

2. The text files will be of various sizes and formats. However they will be of extension .txt or .doc only - ie. basic text files which would open in notepad/wordpad or word documents which would open in MS Word. What I seek ideally is code very similar to yours as above for a .jpg insertion but which will handle .txt.and .doc.

camoore

Wales, UK

[Siddharth Rout]

That method for inserting a picture worked fine, but the code I needed was

Of Course Camoore. I wouldn't know how have you defined your objects. It was just an example

Similarly use this for the text file. Replace oXLWB with the relevant object that you are using to define your workbook Also change the name of the text file to the relevant text file with relevant path...

oXLWB.OpenText Filename:="C:\MyFile.Txt"

Hope this helps...

[camoore]

Koolsid, here is a section of my code:

  'now to insert a picture at sheet 2
    
  Set oXLSheet = oXLBook.Worksheets(2)       'Work with the second worksheet
    
  oXLApp.Visible = True                      'Show it to the user
  
  oXLSheet.Pictures.Insert ("C:\Ali.jpg")
  
  
  'now to insert a text file at sheet 3
  
  Set oXLSheet = oXLBook.Worksheets(3)       'Work with the third worksheet
    
  oXLApp.Visible = True                      'Show it to the user
  
  oXLBook.OpenText FileName:="C:\RAILPROJECT\STATIONLIST.txt"

The first few lines are the picture insertion into sheet 2, and this works fine.

The last 3 lines are my attempt to implement your suggested code for insertion of a .txt document into sheet 3. It does not run for me. I get an error "438. Object doesn't support this property or method". Using debug, the last line oXLBook.OpenText etc. is highlighted in yellow as the error source.

Am I doing something basically wrong please?

camoore

Wales, UK

[Siddharth Rout]

Open the text file first and then insert a new worksheet and then insert the picture

[camoore]

koolsid,

Per your suggestion, I have added code to open the file before opening the third worksheet but I still get that same error at the oXLBook line. I would appreciate your further suggestion(s). This is a first time for me to try this, so it is highly likely I am making a simple error.

camoore

Wales, UK

 
  'now to insert a text file at sheet 3

  Open  "C:\RAILPROJECT\STATIONLIST.txt" for Input as #1 
  
  Set oXLSheet = oXLBook.Worksheets(3)       'Work with the third worksheet
    
  oXLApp.Visible = True                      'Show it to the user
  
  oXLBook.OpenText FileName:="C:\RAILPROJECT\STATIONLIST.txt"

[Siddharth Rout]

Ok, show me your complete code...

[camoore]

Koolsid,

At attachment is a .zip of the program. This small program, as its name implies, is to develop and test techniques for the automation of Excel within VB. When the technique is proven here, I use that code in the application.

The code in question is at COMMAND 2 of this program. Ignore COMMAND 1 code.

Please note that as it stands the Excel save and shut-down code at the end is by-passed (by a goto line1000 statement).

Thank you for offering to look at this.

camoore

Wales, UK

[Siddharth Rout]

Ok I haven't seen your code yet...

I was creating a sample for you...

See if this is what you want... BTW I have used Late Binding instead of Early Binding as you have done...

Private Sub Command1_Click()
    Dim oXLApp As Object, oXLBook As Object, oXLSheet As Object

      '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    
      '~~> If no Excel is opened then Create a new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oXLApp.Visible = True
    
    '~~> Open the Text File
    Set oXLBook = oXLApp.Workbooks.Open(FileName:="C:\RAILPROJECT\STATIONLIST.txt")
    
    oXLBook.sheets.Add(After:=oXLBook.Worksheets(oXLBook.Worksheets.Count)).Name _
    = "Sheet" & oXLBook.Worksheets.Count '<~~ Add 2nd Sheet
 
    oXLBook.sheets.Add(After:=oXLBook.Worksheets(oXLBook.Worksheets.Count)).Name _
    = "Sheet" & oXLBook.Worksheets.Count '<~~ Add 3rd Sheet
    
    '~~> insert Picture in Sheet3
    Set oXLSheet = oXLBook.Worksheets(3)
    oXLSheet.Pictures.Insert ("C:\Ali.jpg")
    
    '~~> Rest of the code
End Sub

[camoore]

Thanks Koolsid. I tried this code, and it gets me close to the need. The remaining problem is that the .txt file seems to get loaded into a sheet of its own up front in the Excel file. What we very much want to do is to create the earlier sheets first (say 1,2,3) and then have the .txt file inserted into sheet 3 (say). Reason is that the program already has done a lot of work on sheet 1 and the .txt insertion into sheet 2 or 3 is by way of augmenting/complementing sheet 1. Until the work is done with sheet 1, it will not be known which .txt and which .jpg files need to be inserted into sheets 2 and 3.

Hoping that I have explained the need adequately, do you have a further suggestion? I think that your last code comes very close to doing what I wish.

camoore

Wales, UK

[si_the_geek]

Create sheet 1 etc as normal, then open the text file as a separate workbook, and copy/move the sheet to your proper workbook.

[Siddharth Rout]

The remaining problem is that the .txt file seems to get loaded into a sheet of its own up front in the Excel file.

Camoore, I tried it and it does everything in one workbook.... show me the final code that you are using....

[camoore]

I regret that have little knowledge of Excel automation, and what I have is mostly thanks to Si's tutorial. The Koolsid method does indeed generate three sheets in one workbook, but the inserted .txt document is in the first while I need to insert it into sheet 3. Also the sheets are not entitled "sheet 1", "sheet 2" etc. but are given names derived from the .txt file name.

Si's suggestion above looks worth trying, but Si could you please give me some basic code to experiment with. I can use Koolsid's method to generate a new workbook with 1 sheet and the .txt document inserted, but how please to copy/move it to become, say, sheet 3 in the desired workbook (which at present has three blank sheets)?

Is there somewhere one can read up a lot more about Excel automation? I am conscious that my questions are probably very fundamantal, but it seems a difficult area about which to find information. None of my VB books mentions Excel automation at all.

An extra question : Having generated an Excel workbook of 3 sheets and closed it, how can I re-open it under automation so as to gain access to a sheet and add more information to it / amend information already on it. I think I will be OK once I have code to get the workbook open again.

camoore

Wales, UK

[Siddharth Rout]

The Koolsid method does indeed generate three sheets in one workbook, but the inserted .txt document is in the first while I need to insert it into sheet 3.

The hint lies in the code that I gave...

Try this

Private Sub Command1_Click()
    Dim oXLApp As Object, oXLBook As Object, oXLSheet As Object

      '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    
      '~~> If no Excel is opened then Create a new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oXLApp.Visible = True
    
    '~~> Open the Text File
    Set oXLBook = oXLApp.Workbooks.Open(Filename:="C:\RAILPROJECT\STATIONLIST.txt")
    
    oXLBook.Sheets.Add
 
    oXLBook.Sheets.Add
    
    '~~> insert Picture in Sheet1
    Set oXLSheet = oXLBook.Worksheets(1)
    oXLSheet.Pictures.Insert ("C:\Ali.jpg")
    
    '~~> Rest of the code
End Sub

Edit:

Also the sheets are not entitled "sheet 1", "sheet 2" etc. but are given names derived from the .txt file name.

You can specify that after you add the sheets. That is not a problem.... can you show me the text file which has the names?

[si_the_geek]

You should be able to work out the code on your own with little/no assistance beyond what is covered in my tutorial, and in doing so you will learn more (and be able to do more yourself next time).

In terms of moving a sheet to a different position, record a macro of moving the sheet (drag it from one place to another). The "How do I ...?" section of the tutorial explains how to convert macros to automation code.

An extra question : Having generated an Excel workbook of 3 sheets and closed it, how can I re-open it under automation so as to gain access to a sheet and add more information to it / amend information already on it. I think I will be OK once I have code to get the workbook open again.

Erm... have you looked in the tutorial?

[camoore]

Thank you Koolsid. The penny starts to drop. I will try this tonight and report back to thread, hopefully closing it.

Si - you are quite right to draw attention to your tutorial. I HAD of course read it, but I missed the line at #4 which dealt with opening an existing workbook. Mea maxima est culpa.

I DO try to work out code on my own, and usually suceed. I certainly do not expect others to write full code for me. However when doing something for the first time it is very helpful to have a piece of basic working code upon which to build the needs of an application. That is the format of a lot of good VB books from which I learnt most of (what little) I now know.

Thank you both for now. Will post when tried.

camoore

Wales, UK

[si_the_geek]

... However when doing something for the first time it is very helpful to have a piece of basic working code upon which to build the needs of an application. ...

I agree with that, and is the way most of us prefer to do it.

In the case of most things to do with Excel automation it only takes a few seconds to get Excel to create the example code for you - assuming that it is something you can do manually in Excel, such as moving a sheet, or creating a graph.

From there you only need a few steps (which are detailed in the tutorial) to make it fully suitable for your program.

[camoore]

Koolsid,

Based on your suggested code, I am trying the following :

Private Sub Command4_Click()

    Dim oXLApp As Object, oXLBook As Object, oXLSheet As Object

      '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    
      '~~> If no Excel is opened then Create a new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oXLApp.Visible = True
    
    '~~> Open the Text File
    Set oXLBook = oXLApp.Workbooks.Open(FileName:="C:\RAILPROJECT\STATIONLIST.txt")
    
    oXLBook.Sheets.Add
 
    oXLBook.Sheets.Add
    
    '~~> insert Picture in Sheet1
    Set oXLSheet = oXLBook.Worksheets(1)
    oXLSheet.Pictures.Insert ("C:\Ali.jpg")
    
Line10:

'GoTo Line100
    
  Set oXLSheet = Nothing                      'disconnect from the Worksheet
  
  oXLBook.SaveAs "C:\My Documents\XLTEST.xls" 'Save (and disconnect from) the Workbook"
  
  oXLBook.Close SaveChanges:=False
  
  Set oXLBook = Nothing
  
  oXLApp.Quit                                  'Close (and disconnect from) Excel
  
  Set oXLApp = Nothing
    
Line100:

End Sub

If I stop the program at line 10 I get pretty much what is wanted - a workbook with three sheets - sheet1, sheet2 and stationlist. The picture however is inserted into sheet 2 and not sheet 1. Am not sure why?

The problem comes when I run the rest of the code to save the workbook in XLTEST.xls. The code runs and a save takes place, but the saved XLTEST.xls file is very small (1k) contains only 1 sheet (named XLTest) and no inserted document or .jpg image.

I have tried everything I can think of to no avail, and there would be grateful for your thoughts. Probably it is a basic error on my part.

camoore

Wales, UK

[si_the_geek]

You are making assumptions about which position oXLBook.Sheets.Add will add the new sheet to, but if it matters to you then you should specify it via the parameters (I can't remember exactly what they are, see the help for .Add for details and examples).

In terms of the save issue, you probably need to specify that instead of saving as a Text file (which is likely to be assumed, as that is what you loaded). The help for .SaveAs should tell you all you need to know.

[Siddharth Rout]

Are you trying this?

Sub Sample()
    Dim oXLApp As Object, oXLBook As Object, oXLSheet As Object
    Const xlNormal = 1
    
    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    
    '~~> If no Excel is opened then Create a new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oXLApp.Visible = True
    
    '~~> Open the Text File
    Set oXLBook = oXLApp.Workbooks.Open(FileName:="C:\RAILPROJECT\STATIONLIST.txt")
    
    '~~> Add Sheets
    oXLBook.Sheets.Add
    oXLBook.Sheets.Add
    
    '~~> insert Picture in Sheet1
    Set oXLSheet = oXLBook.Worksheets("Sheet1")
    oXLSheet.Pictures.Insert ("C:\Ali.jpg")
    
    '~~> Save File
    oXLBook.SaveAs FileName:= _
    "C:\My Documents\XLTEST.xls", _
    FileFormat:=xlNormal
    
    '~~> Close
    oXLBook.Close SaveChanges:=False
    
    '~~> Clean Up
    Set oXLSheet = Nothing
    Set oXLBook = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing
End Sub

[camoore]

Thank you Koolsid and Si. The modified code from Koolsid solved the problem - I needed that extra code at the save as line to ensure that I saved in Excel and not in something else (in view of the small size - 1K - of the file it was probably a text file, though it had the extension .xls). The save as process appears to have been "confused" because of the insertion of a .txt file right up front when the Excel was created. In future I will always use Koolsid's code for save as to avoid such a problem as I encountered.

Am grateful also to Si for mentioning macro recording. Had never done this, and did not know what a powerful and useful tool this is. Using the macro method, I came up with this code to re-name an Excel worksheet :

 '^^> how to re-name a worksheet
    oXLBook.Sheets("STATIONLIST").Select
    oXLBook.Sheets("STATIONLIST").Name = "Sheet3"

Unfortunately it does not appear as if (my version of) Excel allows the insertion of .txt documents into a sheet (though it does cater for insertion of pictures, and much else). Hence I could not record a macro for text document insertion, but Koolsid's method solves that.

Now I will endeavour to apply what I have learnt from you chaps to my application, and it is time to mark this thread as resolved.

Thanks as always for your patient assistance,

Regards,

camoore

Wales, UK

[camoore]

By way of concluding this post, below I am showing the code which I have written and got working, thanks to Koolsid and Si, in case it may be of use to another forum member.

What it does : 1. It generates a new Excel file (workbook) containing 3 sheets named sheet1/2/3. 2. The workbook ends up saved in the path CHARXCL (in this case, CHARXCL is a unique path based upon computer date and time, but it could be any other path). 3. Sheet 1 is a blank sheet (to be worked on later and data added). Sheet 2 contains inserted a photo image .jpg. The path to this image is specified in the code. Sheet 3 contains a .txt text document in its entirety. This text document MUST be at path C:\RAILPROJECT\XLTEXT.txt prior to the running of this code.

camoore

Wales, UK

    Dim oXLApp As Object                    'Declare the object variables

    Dim oXLBook As Object                   'Declare the object variables

    Dim oXLSheet As Object                  'Declare the object variables
 
    Dim oXL As Object                       'Declare the object variables
    
    Const xlnormal = 1

    Dim CHARXCL as String                    'used for Excel file name/path    
    
    CHARXCL = Date & "_" & Time             'read computer date and time into CHARXCL

    If InStr(CHARXCL, "/") Then CHARXCL = Replace(CHARXCL, "/", "-")
                                            'now construct a suitable file
                                            'name/path for the Excel record to be
                                            'generated.
    If InStr(CHARXCL, ":") Then CHARXCL = Replace(CHARXCL, ":", "")

    CHARXCL = "C:\RAILPROJECT\SAVED EXCEL\SS" & CHARXCL & ".xls"
    'this will be the file name and path
    'eg. C:\RAILPROJECT\SAVED EXCEL\SS 051209_140934.xls 
    
    Set oXLApp = CreateObject("Excel.Application") 'open a new Excel application
   
    oXLApp.Visible = False                         'do not show it to user (saves time)
    
    'Open the Text error report text file XLTEXT.txt and insert this into a
    'workbook sheet.
    
    Set oXLBook = oXLApp.Workbooks.Open(FileName:="C:\RAILPROJECT\XLTEXT.txt")
    
    'note inverse order of adding sheets and numbering
    
    oXLBook.Sheets.Add(After:=oXLBook.Worksheets(oXLBook.Worksheets.COUNT)).Name _
    = "Sheet2"   'Add 2nd Sheet to workbook, to be named Sheet 2
 
    oXLBook.Sheets.Add(After:=oXLBook.Worksheets(oXLBook.Worksheets.COUNT)).Name _
    = "Sheet1"   'Add 3rd Sheet to workbook, to be named Sheet 1
 
    
    'insert a Picture in Sheet2
    
     oXLBook.Sheets(2).Pictures.Insert("C:\RAILPROJECT\Ngd.jpg").Select
    
    'Now re-name the worksheet "XLTEXT" as "Sheet3"
    
     Set oXLSheet = oXLBook.Worksheets("XLTEXT")  'Work with the first worksheet
    
     oXLBook.Sheets("XLTEXT").Name = "Sheet3"     'rename it as "sheet 3"
     
     'now close the .xls file and save it
    
     Set oXLSheet = Nothing                     'disconnect from the Worksheet
  
    'next delete the existing file, if there is one.
  
    If Dir(CHARXCL) <> "" Then                  'ie. file does already exist
  
        Kill (CHARXCL)
        
    End If
  
  
    'Save the new .xls File                     'nb. File Format code at the end of line
  
    oXLBook.SaveAs FileName:=CHARXCL, FileFormat:=xlnormal

    oXLBook.Close SaveChanges:=False
  
    Set oXLBook = Nothing
  
    oXLApp.Quit                                  'Close (and disconnect from) Excel
  
    Set oXLApp = Nothing