Import to Excel

[netaps]

In my application, I get a comma delimited file which I need to import into a worksheet. Every day a new comma delimited file comes which I need to import into the same worksheet but store it in a separate sheet for every day. I do not know VB, can you help me with the VB script to do this?

[Tom Schreiner]

Do you need VBA for this?
Why not use the import text file abilities of Excel?
From your Worksheet Menu Bar:
Data, Get External Data, Import Text File...
Does this help?
Tom

[netaps]

Probably I need to be more clear on this. I have a Java appliction running and this application needs to call Excel and probably fire off some routine in Excel which imports the comma delimited file into a worksheet. The Java application keeps getting these comma delimited files every day, and the application needs to import this data and save it in the same worksheet but in different sheets day-wise.

[Tom Schreiner]

Java too me is something like a cup of coffee.
I'm assuming we're using automation as is.(OLE)
Please post your script if you don't mind.
Or am I missing your point here?
Tom

[netaps]

Let's forget Java for the moment. What if I need to do the same thing by loading Excel and running a VBA script? What would be the script? If that works, I'll write the necessary code to run that from Java.

[netaps]

Well one likely solution could be to use Java Excel API - an open source Java API which allows Java developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically. The URL for that is http://www.andykhan.com/jexcelapi/

However, if anyone has a better solution, do let me know.

[Tom Schreiner]

Hi again.
This macro will open a standard getopenfile dialog, and import the text file to a new worksheet named after the current date(mmddyy)
Will need a bit of customization, perhaps.

Sub Import1()

On Error Resume Next

Dim YourFile, RenameSheet

'if the file name is always the same and in the

'same path, replace the next two lines with:

'YourFile = Path and Filename    Ex. "C:YourPathFileName.???"

YourFile = Application.GetOpenFilename

If YourFile = False Then Exit Sub



ActiveWorkbook.Worksheets.Add

ActiveSheet.Name = Format(Now, "mmddyy")

If Err.Number = 1004 Then

    Err.Clear

    RenameSheet = InputBox("You already have a sheet named " & _

    Format(Now, "mmddyy") & "." & "Please enter another name.")

    If RenameSheet = "" Then Exit Sub

End If

With ActiveSheet.QueryTables.Add(Connection:= _

    "TEXT;" & YourFile, Destination:=Range("A2"))

    .Name = Format(Now, "mmddyy")

    .FieldNames = True

    .RowNumbers = False

    .FillAdjacentFormulas = False

    .PreserveFormatting = True

    .RefreshOnFileOpen = False

    .RefreshStyle = xlInsertDeleteCells

    .SavePassword = False

    .SaveData = True

    .AdjustColumnWidth = True

    .RefreshPeriod = 0

    .TextFilePromptOnRefresh = False

    .TextFilePlatform = xlWindows

    .TextFileStartRow = 1

    .TextFileParseType = xlDelimited

    .TextFileTextQualifier = xlTextQualifierDoubleQuote

    .TextFileConsecutiveDelimiter = False

    .TextFileTabDelimiter = False

    .TextFileSemicolonDelimiter = False

    .TextFileCommaDelimiter = True

    .TextFileSpaceDelimiter = False

    'change this array to match the number of columns in your text file

    'and the data types

    'replace the ones with constants if necc

            'xlDMYFormat DMY date or 4

            'xlDYMFormat DYM date or 7

            'xlEMDFormat EMD date or 10

            'xlMDYFormat MDY date or 3

            'xlSkipColumn Skip column or 9

            'xlTextFormat Text or 2

            'xlYDMFormat YDM date or 8

            'xlYMDFormat YMD date or 5

            'xlGeneralFormat or 1

            'for example:  If the first column is a date, 2nd text,

            'then the first two values of the following array would be

            'your choice of 4,7,10,3,8,5 for the first and 2 for the second.

    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

    .Refresh BackgroundQuery:=False

End With

End Sub

[ This Message was edited by: TsTom on 2002-05-13 00:34 ]