Application using Excel, Notepad and vb6
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;
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;
Code:
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;
Code:
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.
Re: Application using Excel, Notepad and vb6
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
:wave:
Re: Application using Excel, Notepad and vb6
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.
Re: Application using Excel, Notepad and vb6
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.
Code:
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
:wave:
Re: Application using Excel, Notepad and vb6
Re: Application using Excel, Notepad and vb6
Quote:
Is that code VB6. net?
OK this is VB6 forum. So guess what code this is ;)
what are YOU using, vb6 or .Net?
:confused:
Re: Application using Excel, Notepad and vb6
Sorry, I made a mistake. I tried it again and it worked.
Thank you,
David
Re: Application using Excel, Notepad and vb6
Re: Application using Excel, Notepad and vb6
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.
Code:
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. :D
Re: Application using Excel, Notepad and vb6
One more qeustion. How do I prevent the excel workwork from being ReadOnly?
Re: Application using Excel, Notepad and vb6
check if it is already open before opening it in your code??
Re: Application using Excel, Notepad and vb6
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.
Re: Application using Excel, Notepad and vb6
vb Code:
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
Re: Application using Excel, Notepad and vb6
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:confused: This is fun, but I don't get it. help
Re: Application using Excel, Notepad and vb6
Quote:
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 ) ;)
:wave:
