Import to Excel

netaps

New Member
Joined
May 11, 2002
Messages
4
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.<pre>

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</pre>
This message was edited by TsTom on 2002-05-13 00:34
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top