Re: Is it possible to load the excel file by using external table without converting it into csv comma delimited file

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Jul 2006 08:07:21 -0700
Message-ID: <1152630441.200471.215460@b28g2000cwb.googlegroups.com>

Khurram wrote:
> Charles Hooper wrote:
> > I guess that I am not understanding.
> >
> > Are you trying to bring data from the Oracle database into Excel? If
> > so, create an ODBC connection on the computer for the Oracle database,
> > then use Microsoft Query (Import External Data in Excel) to retrieve
> > inforrmation from the database by using a SQL statement.
> >
> > Or, are you trying to insert data into the Oracle data using the
> > information in Excel? If so, you can either create a maco to feed the
> > information into Oracle, or use Microsoft Access to link to the Excel
> > spreadsheet and append the contents of the spreadsheet to a table in
> > Oracle. A macro in Excel could be used to output a text file that
> > terminates each of the cell contents with <CELL>, if desired.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Hi Charles
>
> Thanx a lot for yours effort , i want to access the excel spreadsheet
> data from external tables that do not reside in the database, and can
> be in any format for which an access driver is provided.The external
> data can be queried directly and in parallel using SQL.
>
> But external table requires fields termination seprator which is not in
> excel ,to achieve it one need to convert excel spreadsheet to CSV comma
> delimited file (We have monthly excel format outsource file),i wana
> make users to avoid converting excel spread sheet to CSV format
> repeteadly every month.
>
> Khurram
> (',' delimited column) format

Rough example code of a macro to dump the contents of the Excel spreadsheet to a file deliminated by "<CELL>": Public Sub ExportSpreadsheet()

    Dim intResult As Integer
    Dim intColumn As Integer
    Dim strFilename As String
    Dim strWorkbookname As String
    Dim strSheet As String

    Dim intLastRowChecked As Integer
    Dim intFoundBlankRow As Integer
    Dim strOut As String
    Dim strFilenameOut As String
    Dim intFileNum As Integer

    'Make sure that we don't crash - looks ugly     On Error Resume Next

    strFilename = ActiveWorkbook.FullName

    'Strip off the file path, leaving just the name of the worksheet     strWorkbookname = Right(strFilename, Len(strFilename) - InStrRev(strFilename, "\"))

    'The sheet to look at
    'strSheet = "Sheet1"
    strSheet = ActiveWorkbook.ActiveSheet.Name

    strFilenameOut = strFilename
    strFilenameOut = Left$(strFilenameOut, InStrRev(strFilenameOut, ".")) & "out"

    intFileNum = FreeFile
    Open strFilenameOut For Output As #intFileNum

    intFoundBlankRow = False

    intLastRowChecked = 0

    Do While intFoundBlankRow = False

        intLastRowChecked = intLastRowChecked + 1
        If

Workbooks(strWorkbookname).Worksheets(strSheet).Cells(intLastRowChecked, 1).Value = "" Then

            'Break out of the loop if a blank cell is found in column A - might be the end of the record

            intFoundBlankRow = True
        End If

        If intFoundBlankRow = False Then
            strOut = ""
            For intColumn = 1 To 50  'Number of columns to output
                'Break out of the loop if a blank column is found -
might be the end of the record
                'Remove this if cells can be blank
                If

Workbooks(strWorkbookname).Worksheets(strSheet).Cells(intLastRowChecked, intColumn).Value = "" Then

                    Exit For
                End If
                strOut = strOut &

Replace(Workbooks(strWorkbookname).Worksheets(strSheet).Cells(intLastRowChecked, intColumn).Value, vbCrLf, " ") & "<CELL>"

            Next intColumn
            If strOut <> "" Then
                'Strip the ending <CELL>
                strOut = Left$(strOut, Len(strOut) - Len("<CELL>"))
            End If
            Print #intFileNum, strOut
        End If

    Loop
    Close #intFileNum
    intResult = MsgBox("Exported to " & strFilenameOut, vbInformation) End Sub

As written the macro stops writing the columns when it hits a blank column, and stops writing rows when it hits a blank cell in column A. Note that carriage return-line feed combinations are replace by three spaces. If you include this macro in a spreadsheet, you can easily add a toolbar button to Excel that calls the macro when needed.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jul 11 2006 - 10:07:21 CDT