Application using Excel, Notepad and vb6

[DavidS]

Hi guys, I have just started programming with Visual Basics v6. I am trying to write a program that allows me to read data from five Gerber.txt files and import the data to an Excel workbook.

My first problem is reading the notepad file the Gerber makes. I can open them, but I can't read the data and organise the data types in any format, or infact close the files afterwards.

Heres the start for the code;

Private Sub Control_Click()

Dim FileName As String
Dim DFileName As String
Dim TFileName As String
Dim BFileName As String

BFileName = ("C:\Program Files\Labcenter Electronics\Proteus 7 Professional\SAMPLES\controller1 - CADCAM Bottom Copper")
TFileName = ("C:\Program Files\Labcenter Electronics\Proteus 7 Professional\SAMPLES\controller1 - CADCAM Top Copper")
DFileName = ("C:\Program Files\Labcenter Electronics\Proteus 7 Professional\SAMPLES\controller1 - CADCAM Drill")
FileName = ("C:\Program Files\Labcenter Electronics\Proteus 7 Professional\SAMPLES\controller1 - CADCAM READ-ME")

Shell "notepad.exe " & FileName, vbNormalFocus
Shell "notepad.exe " & BFileName, vbNormalFocus
Shell "notepad.exe " & TFileName, vbNormalFocus
Shell "notepad.exe " & DFileName, vbNormalFocus

End Sub

Here is a sample of a simple file that I want to arrange;

G04 PROTEUS RS274X GERBER FILE*
%FSLAX24Y24*%
%MOIN*%
%ADD10C,0.0120*%
%ADD11R,0.0800X0.0250*%
%ADD12R,0.0800X0.0800*%
G54D10*
X+0Y+0D02*
X+0Y+936D01*
X-116Y+1336D01*
X+883Y+1336D02*
X+867Y+936D01*
X+867Y-986D01*
X+1650Y-986D01*
X+2050Y-1000D01*
X+2050Y-1375D01*
X+2050Y-1500D01*
X+400Y-1500D01*
X+0Y-1500D01*
X+2050Y+0D02*
X+1930Y+125D01*
X+1930Y+936D01*
X+1883Y+1336D01*
X+2050Y-500D02*
X+2914Y-500D01*
X+2914Y+936D01*
X+2883Y+1336D01*
X-1534Y+1336D02*
X-1534Y-474D01*
X-400Y-474D01*
X+0Y-500D01*
G54D11*
X+0Y+0D03*
X+2050Y-1500D03*
X+0Y-500D03*
X+2050Y-1000D03*
X+0Y-1000D03*
X+2050Y-500D03*
X+0Y-1500D03*
X+2050Y+0D03*
G54D12*
X-116Y+1336D03*
X+883Y+1336D03*
X+1883Y+1336D03*
X+2883Y+1336D03*
X-1534Y+1336D03*
X-2793Y-1655D03*
X-2793Y-655D03*
X-2793Y+344D03*
X-2793Y+1344D03*
M00*

One of my first objectives is to convert the data files to a format that my homemade PCB router will understand. I need to organise the data files so that my router will know were the cutting tool should be in relation to the code. At the moment the file formats are for a photoplotter pen.

To make this work properly I need to read a READ ME file first, so that the program will know what parts of the data files needs a conversion.

Here is the file that I'm using the setup the data conversions correctly;

LABCENTER PROTEUS TOOL INFORMATION FILE
=======================================

In case of difficulty, please e-mail [email protected]

Tool set up for Proteus layout 'controller.LYT'.
CADCAM generated at 10:07:17 AM on Monday, March 24, 2008.

File List
---------
Top Copper              : controller1 - CADCAM Top Copper.TXT
Bottom Copper           : controller1 - CADCAM Bottom Copper.TXT
Drill                   : controller1 - CADCAM Drill.TXT

Photoplotter Setup
------------------
Format: RS274X, ASCII, 2.4, imperial, absolute, eob=*, LZO
Notes:  D=Diameter, S=Side, W=Width, H=Height

D10	CIRCLE	D=12th                                      DRAW
D11	SMT	W=80th                H=25th                FLASH
D12	SQUARE	S=80th                                      FLASH

NC Drill Setup
--------------
Format: ASCII, 2.4, imperial, absolute, eob=<CR><LF>, no zero suppression.
Notes:  Tool sizes are diameters. Layer sets are in brackets - 0=TOP, 15=BOTTOM, 1-14=INNER.

T01	40th (0-15)


[END OF FILE]

I'd greatly apprecate the advise and some pointers about how I should proceed with some code.

[zeezee]

Notepad File means a text file right?
If you need to read them, shell wont help. It would open the text file in notepad , but you will need to manually transfer the text

I hope these FAQs would direct you right way. Open the Text File(s) in vb and loop through each file and transfer to excel.

Text File Handling

http://www.vbforums.com/showthread.php?t=342619
http://www.vbforums.com/showthread.php?t=405051

Excel Help
http://www.vbforums.com/showthread.php?t=391665

[DavidS]

Yeah, Notepad means its a text file. I tried both of them yesterday and again today, but no luck. Could you show me an example that won't invoke errors. Something easier to put into practice, so I can figure out what happening.

[zeezee]

Well the FAQs should give you all the things on how to open a file, how to loop through the file contents and how to open excel and manipulate.

What you need to do is, sketch a little plan , a sequence of operations you need to perform.

1. Open a Text File
2. Loop through the file
3. Format each line of data as you need
4. Export the formatted data to excel
5. close file


I will give you sample , but you have to design the whole thing. Specially the Formatting part.

Option Explicit

Dim FileNum As Long
Dim IsFileOpen As Boolean

Private Sub Command1_Click()
   On Error GoTo ErrHandler

    If IsFileOpen = False Then
        FileNum = FreeFile
        Open Text1.Text For Input As #FileNum
        IsFileOpen = True
        
        Command1.Enabled = False
        Command2.Enabled = True
        Command3.Enabled = True
        
    End If
    
   Exit Sub

ErrHandler:
    MsgBox "An error occurred: " & Err.Description, _
        vbApplicationModal + vbExclamation, App.Title

    
End Sub

Private Sub Command2_Click()
    Dim oXLApp As Excel.Application       'Declare the object variable
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
    Dim LineData As String
    Dim i As Long
    
    If IsFileOpen = True Then
        Set oXLApp = New Excel.Application  'Create a new instance of Excel
        Set oXLBook = oXLApp.Workbooks.Add
        
        Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
        
        
        oXLApp.Visible = True               'Show it to the user
        i = 1
        
        While Not EOF(FileNum)
            Line Input #FileNum, LineData
            ' Here you need to format the text as you need and determine whether its needed or not.
            ' If you dont need, discard. If you need, format and export
            oXLSheet.Cells(i, 1).Value = LineData
            i = i + 1
        Wend
        
        
    End If
End Sub

Private Sub Command3_Click()
    If IsFileOpen = True Then
        Close #FileNum
        FileNum = 0
        IsFileOpen = False
        
        Command1.Enabled = True
        Command2.Enabled = False
        Command3.Enabled = False
    End If
End Sub

Private Sub Form_Load()
    IsFileOpen = False

    Command1.Caption = "Open File"
    Command2.Caption = "Export to Excel"
    Command3.Caption = "Close File"

    Command1.Enabled = True
    Command2.Enabled = False
    Command3.Enabled = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Close
End Sub

Add a reference (Project -> References) to Microsoft Excel xx.x Object Library (xx.x should match the excel version you have.)

You need one text box(Text1), three command buttons (Command1,Command2,Command3) in this project
How to test

1.Put a name of a text file in the text box with full path.
2.Open File
3.Export to Excel
4.Close File

[DavidS]

Is that code VB6. net?

[zeezee]

Is that code VB6. net?

OK this is VB6 forum. So guess what code this is

what are YOU using, vb6 or .Net?

[DavidS]

Sorry, I made a mistake. I tried it again and it worked.

Thank you,

David

[zeezee]

No Problem

[DavidS]

Interesting, I had that Command2_click() already, well pretty well the way you wrote it. Here is a code that I found useful. The code opens the workbook and goes to the worksheet that I want.

Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

Set XLApp = Excel.Application
Set XLBook = XLApp.Workbooks.Open("Machine.xls")
XLApp.Visible = True
XLBook.Worksheets("Data").Activate

Please understand that I have just started and know that you could do this a lot quicker than me. I'm going out tomorrow to get a big fat white board to knot out the program.

[DavidS]

One more qeustion. How do I prevent the excel workwork from being ReadOnly?

[westconn1]

check if it is already open before opening it in your code??

[DavidS]

I just throught of something. I need to read four "Text files", starting with a Filename that is (I'm not shouting, but READ-ME.txt) Does it matter if the file named has incorrect UPcase or LOWcase letters?

Thanks Pete,
so how do I close the excel application and save the data stored before the book is closed. I feel that my problem is that I'm new and I don't understand the full picture at the moment, but I'm a fast learner and I'm not afaird of making mistakes.

[westconn1]

xlbook.save
xlapp.quit
set xlbook = nothing
set xlbook = nothing
' set any other objects to nothing

make sure that the user did not have other workbooks open before closing the application

on a windows system filenames are not case sensitive

[DavidS]

So then, workbooks and the program should start from VB6?

If I create a Excel workbook from VB, then modify it (from Excel)> format cells and create procedures to deal with data and then save it and close. I should have a workbook, including worksheets and macros saved?

I think I'm lost in the bushes, just before the trees This is fun, but I don't get it. help

[zeezee]

create procedures to deal with data and then save it and close. I should have a workbook,

You mean create a macro from VB?
I m not sure if its possible

Anyway , if you read the FAQs, you will find the technical details on excel and text files. The design or the steps, would be similar to what I gave.
The part missing in there is Formating the text retrieved from the TXT file and determining where to put it in the Excel file.

But as a start, you could try doing like the example. read each file, put its contents to excel file.
And if you get any problems you can ask . (Preferably in separate threads )