files import

how do i get data from datbase or comma delimilted or tab delimilted files or excel

when i click on the import button i should be be to open the required file.
get the first record from the file to the corel document for eg

i can click next to goto next record and also cick on previous to go the previous record just in case if the user wants to chnage something
in the previuos record

now i have a button name cmd_import_click

and i have the following code
which opens an excel file how do i connect the above button to open all the files of different type and diffrent names .
something like a browse button will do thake me to the location and open the file for me.and do the above stuff when i click
next and previous button.i know printmerge allows u to do something like this but printmerge takes all the values(fields) in from the file
Sub ReadExcel()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim s As String

' Open the connection to an Excel file
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\data.xls;" & _
"Extended Properties=""Excel 8.0; HDR=No;"""

rs.Open "SELECT * FROM [Tabelle1$a1:a13]", cnn, _
adOpenForwardOnly, adLockReadOnly

s = ""

While Not rs.EOF
If s <> "" Then s = s & vbCrLf
s = s & rs!F1
rs.MoveNext
Wend

rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing


End Sub

i guess this forum is not happening . is there any other links or websites which will help me for the above problem.

thanks

ithanks in advance

[Alex]

Sorry for "ignoring" you. It's just your problem sounds quite complicated and it would require a lot of time to address completely. From your description it seems that you would like the whole program created for you rather than try to solve a particular problem or two you bumped into. I just wanted to get some free time to get to your request properly.

Unfortunately I've been quite busy lately, so I couldn't reply to your message even though some other people's questions were answered because they were much simpler.

I guess you need to clarify a bit more of what you are trying to do. What do you mean "allow the user to do some formatting"? Do you mean that after the record value is merged into the document, the user goes in the document, moves text around, changes fonts and colors, etc and the macro somehow waits for this to finish before he proceeds with the next record? What happens to the record once it is processed and we move on to the next one? Do we save the document, create a new page, print it?

I guess I would suggest you to split your questions in parts and work on them in turn. First, try describing the workflow a bit more. What exactly you want to do, and how (what the user interface looks like in your opinion, how the user interacts with the program, what happens to the data, how it is inserted in the document (is there a template document which you are working on, or the text is just created at specified locations on the page) and things like this.

Then we'll work over the task one step at a time. Otherwise, trying to answer your question would mean just go and create the whole solution for you

[quote="Alex"]
thanks for looking at my problem i really really apreciate it from the bottom of my heart

thanks Alex ... for lookign at it finally

i have a corelscript which i found on ths forum and it works well .it opens the file open dialogue box and i can browse through it.but how do i get records one at a time.
that button should allow to browse,open files which can be anywhere on the harddrive .the files can a comma delimteed file or tab delimited file or excel sheet or database.
first time when the file is open it gets the first record from the file and places in a corel document
.

thanks

i did try a few things .

Sub cmd_import_click_Click()
    Dim sFileName As String
    Dim sobj As Object
    Dim strRec As String
    Dim iFile As Integer, strLog As String, FileName As String
    
    sFileName = CorelScriptTools.GetFileBox("text Files (*.txt; *.rtf; *.xls; *.csv; *.mdb)|*.txt; *.rtf; *.xls; *.csv; *.mdb", "Select a file to import")
    If sFileName = "" Then
        ' User canceled. Just exiting...
        Exit Sub
    End If
     iFile = FreeFile()
     'FileName = "C:\testfile.txt"
     Open sFileName For Binary Access Read As #iFile
     strLog = Space(FileLen(sFileName))
     Get #iFile, , strLog
     Close #iFile
MsgBox strLog
TextBox1.Value = strLog
   Exit Sub         -----i m trying to come out of the sub forcefuly.

End Sub

my corel get hanged completely .its says not responding
i have a form as i told before and i am just printing the values from the file in a textbox in the form.
i tried to debug and just after exit sub and end sub it gets hanged.
i have a button called exit also in the form
and i tried even putting unload me for the form but the same thing.

help will be apprecaited.

thanks

[Alex]

Well, you aren't doing anything wrong. There is just a bug with CorelScriptTools.GetFileBox method which causes the application to freeze after it is used. To overcome the problem, you need to add a few null characters to the end of the filter string...

Ok, I have worked on your macro a bit and here is what I came up with. Again, I'm still not 100% sure what you need to do with the data you pull from the database, so bear with me here. I just add records to a paragraph text line by line. I hope this is enough for you to illustrate the point and get you started.

So, here is what I have done so far:

1. When launched, the macro shows a file box to pick the database file name. This could be an MS Access database (*.mdb), MS Excel spreadsheet (*.xls) or plain text files (tab or comma delimited).

2. After this, the macro reads in all the records from the database and creates an internal list of all the records read. I have created a class clsDataRecord to represent the data read from the database.

3. After the data is read successfully, the Browse data form is shown which allows you to go through data records and then add a paragraph text line for the selected record.

When you select an Excel or MS Access file, it might contain more than one data table (worksheet), so another form will pop up asking you to select the data table to import.

My macro (the full GMS file attached below) contains four main modules:

- The main code module with the macro and database import functions
- The clsDataRecord class module
- Two forms - TableSelectForm and BrowseDataForm.

The project must have a reference to Microsoft ActiveX Data Objects library in order for it to compile and work (the library is used to interface with the database). (see the attached screenshot of the References dialog in VBA).

The two forms look like in the attached image...

[Alex]

Now the source code of the main module:

Option Explicit
    
Public Records As Collection

' Data to communicate with TableSelectForm
Public rsTables As ADODB.Recordset
Public TableSelected As String

Sub ImportData()
    Dim DBFile As String
    Dim FileType As String
    Dim n As Integer
    Dim sFilter As String
    
    DBFile = ""
    
    On Error GoTo ErrHandler
    
    sFilter = "Database Files (*.txt; *.xls; *.csv; *.mdb)|*.txt;*.xls;*.csv;*.mdb"
    ' To work around a bug with GetFileBox which causes the application to freeze,
    ' add some magic data to the filter string
    sFilter = sFilter & String$(5, vbNullChar)
    
Retry:
    Set Records = Nothing
    Do
        DBFile = CorelScriptTools.GetFileBox(sFilter, "Select a file to import", 0, DBFile)
        If DBFile = "" Then Exit Sub
        
        n = InStrRev(DBFile, ".")
        If n <> 0 Then
            FileType = UCase(Mid(DBFile, n + 1))
        Else
            FileType = ""
        End If
        
        Select Case FileType
            Case "TXT", "CSV"
                Set Records = ImportTextFile(DBFile)
            Case "XLS"
                Set Records = ImportExcelFile(DBFile)
            Case "MDB"
                Set Records = ImportAccessFile(DBFile)
            Case Else
                MsgBox "Unsupported file name specified", vbCritical
        End Select
        
        If Not Records Is Nothing Then
            If Records.Count = 0 Then
                MsgBox "The file doesn't contain any valid data", vbCritical
                Set Records = Nothing
            End If
        End If
    Loop While (Records Is Nothing)
    
    ' Show the browse data dialog
    BrowseDataForm.Show vbModal
    
ExitSub:
    Exit Sub
    
ErrHandler:
    Close ' Close all files that we might have opened
    If MsgBox("Error occured: " & Err.Description, vbCritical + vbRetryCancel) = vbCancel Then
        Resume ExitSub
    Else
        Resume Retry
    End If
End Sub

Private Function ImportTextFile(ByVal DBFile As String) As Collection
    Dim col As New Collection
    Dim rec As clsDataRecord
    Dim sLine As String
    Dim Fields() As String
    
    Open DBFile For Input As #1
    While Not EOF(1)
        Line Input #1, sLine
        sLine = Replace(Trim$(sLine), vbTab, ",")
        If sLine <> "" Then
            Fields = Split(sLine, ",")
            If UBound(Fields) > 1 Then
                Set rec = New clsDataRecord
                rec.Name = Fields(0)
                rec.Country = Fields(1)
                rec.Position = Fields(2)
                col.Add rec
            End If
        End If
    Wend
    Close #1
    Set ImportTextFile = col
End Function

Private Function ImportExcelFile(ByVal DBFile As String) As Collection
    Dim col As New Collection
    Dim rec As clsDataRecord
    
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim s As String
    
    ' Open the connection to an Excel file
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBFile & ";" & _
             "Extended Properties=""Excel 8.0; HDR=No; IMEX=2;"""
    
    Set rsTables = cnn.OpenSchema(adSchemaTables)
    TableSelectForm.Show vbModal
    Set rsTables = Nothing
    If TableSelected = "" Then Exit Function
    
    rs.Open "SELECT F1,F2,F3 FROM [" & TableSelected & "]", cnn, _
            adOpenForwardOnly, adLockReadOnly
    
    While Not rs.EOF
        If Not IsNull(rs!F1) And Not IsNull(rs!F2) And Not IsNull(rs!F3) Then
            Set rec = New clsDataRecord
            rec.Name = rs!F1
            rec.Country = rs!F2
            rec.Position = rs!F3
            col.Add rec
        End If
        rs.MoveNext
    Wend
    
    rs.Close
    cnn.Close
    
    Set ImportExcelFile = col
End Function

Private Function ImportAccessFile(ByVal DBFile As String) As Collection
    Dim col As New Collection
    Dim rec As clsDataRecord
    
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim s As String
    
    ' Open the connection to an Excel file
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DBFile & ";"
    
    Set rsTables = cnn.OpenSchema(adSchemaTables)
    TableSelectForm.Show vbModal
    Set rsTables = Nothing
    If TableSelected = "" Then Exit Function
    
    rs.Open "SELECT * FROM [" & TableSelected & "]", cnn, _
            adOpenForwardOnly, adLockReadOnly
    
     While Not rs.EOF
        If Not IsNull(rs(1)) And Not IsNull(rs(2)) And Not IsNull(rs(3)) Then
            Set rec = New clsDataRecord
            rec.Name = rs(1)
            rec.Country = rs(2)
            rec.Position = rs(3)
            col.Add rec
        End If
        rs.MoveNext
    Wend
    
    rs.Close
    cnn.Close
    
    Set ImportAccessFile = col
End Function

Here the global variable Records contains a collection of clsDataRecord objects to contain the data read from the database. This collection is used later by the Browse Data form.

The other two global variables (rsTables and TableSelected) are used to pass the list of available database tables to the Select Table form and return the name of the table selected.

After the database file name is specified, I inspect the file's extension and decide how to process the file. I have three functions to read plain text records, excel files and access databases. In fact, Excel and Access reading functions are very similar and can be combined into one, but I left them separate in case someone needs only a particular version for their own projects...

I read text files myself using VBA's File I/O functions. I could have used ADO to read the files too as if it was a database file, but I decided to demonstrate how to use the native VBA functions for this... When a line of text is read, I first replace all tabs with commas (if there are any) and then break apart each substring separated by commas into a string array and access them independently. This way, I can read both comma and tab delimited data the same way... There are complications of course (e.g. in tab delimited files, records containing commas will be screwed up, but I guess for your particular project this is not an issue).

Excel/Access files are read using ADO. First I open a database connection, then see the description of the database to determine what data tables it contains and show the form to let the user pick a table. Once this is done, the table is opened up and the data polled using a simple SQL query.

Excel always gives fixed names for columns F1, F2, F3, ... (unless the first row of data is treated as header row). Access, however, could have any field names specified in the table definition. So I just accessed the fields by ordinal index, rather than a name (for simplicity and to illustrate another way of doing it, of course ).

Finally, for each data record read, an instance of clsDataRecord object is created and initialized with the field data. Then the object is added to the collection and the collection is eventually returned form the function.

The data object collection is then passed on to the Browse Data form which uses it to populate the text boxes and allow the user to navigate through records.