Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Import to Excel

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •