Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 4 of 4

Thread: Importing: Comma Delineated template

  1. #1
    Join Date
    23rd July 2003
    I'm going out on a limb here as I am not an expert in Excel, so I'll ask a few questions in the hope that one of them makes sense to someone who is:

    I put together a database program that uses some specialized hardware and outputs several comma delineated files (the delineator can be changed if necissary as well as file structure if it is needed). Basically I would like to create an option that allows these files to be input into Excel, preferably with the first line of the file as the Worksheet name and all of them in the same Workbook (how large can a workbook be?).

    When these are imported, I don't want the end user to have to go through the option tree for importing a comma delineated file. Is there a way to set up a template that will use the same options every time and that will read in each of these files? Maybe using VBA?

    Lastly, I'm looking for a way to import these files into worksheets that already have the operations entered in. For example, if I want to subtract C3-C2 in cell D1, that equation will already be in cell D1 of every Worksheet.

    Thanks for any help anyone can offer! I can sure use it!

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    26th June 2003
    Maryland, USA
    Hi Andy and Welcome

    Not sure which version of Excel you're using but in later versions a comma delimited file with a .csv extension opens directly in excel without going through the field setup process with most text, dates, numbers, etc recognized for what they are supposed to be. If your database outputs the file with a different extension, try changing it to .csv and see what happens.

    As for max size, its 256 columns (fields) by 66,536 rows (records). . . depending on the available resources in your PC, you could get an "out of memory" error if there is too much data.

    To import into worksheets that already have data/formulas, etc., you could open the .csv file in a new workbook (to get it in excel format), then cut /paste the relevent sections or the whole thing into your template workbook. That could all be automated with VBA.

    Someone here on the board can probably get more specific if you need it, but we'll probably need to see an example of your data file and exel workbook.

    Hope this gets you started...


    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    1st February 2003
    Now Sydney - Go the AB\'s
    Welcome to the Forum - Here you should find the solution to your problem......

    Firstly it is simple to run a macro which can be fired by a number of means to delimit a file. ie

    Selection.TexttoColumns Destination:=Range("A1"), DataType:=xlDelimited, _

    This will delimit a range of data by using a comma delimiter. The data will then be placed accross the colums in row A.

    I'm not sure how you intend to input your data but again you can import data as shown below - this is from a text file.

    Sub Rectangle2_Click()

    ' setting up constants

    Const ForReading = 1
    Const TristateUseDefault = -2
    'dimming new objects etc

    Dim newobject, file, data, datastring
    'creating a new object
    Set newobject = CreateObject("Scripting.FileSystemObject")

    'setting up the file so it can be found

    Set file = newobject.getfile(ThisWorkbook.path & Application.PathSeparator & "textfile.txt")
    ' opening up the textbox for reading only
    Set data = file.OpenasTextstream(ForReading, TristateUseDefault)
    'setting the data to read the line of info
    datastring = data.readline
    'closing the textbox

    'now loading the datastream into the cell and delimiting it
    With Range("A1")
    .Value = datastring
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    End With
    End Sub

    You would then need to have the math either set into the cell location or use VBA to wright the equation you were after.

    and to re-name the work sheet, this would be done after the data is delimited and set simply by the following:

    Sheets("sheet1").Name = Range("A1").Value
    ActiveSheet.Name = Range("A1").Value

    Hope this is what you were after - Phil

    PS :biggrin:

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    23rd July 2003
    Thank you both for your answers, between the two of them I should have a good go of things! I'll be working on it first thing in the morning.


    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Save as Space delineated Text
    By JustAnotherGuy in forum Excel General
    Replies: 7
    Last Post: February 7th, 2004, 16:25
  2. Replies: 2
    Last Post: June 20th, 2003, 22:03
  3. Importing .txt comma delineation problem [SOLVED]
    By draley in forum Excel General
    Replies: 2
    Last Post: April 5th, 2003, 08:41
  4. saving as a tab delineated .dat file in excel
    By Karmal64 in forum Excel General
    Replies: 1
    Last Post: March 12th, 2003, 07:27


Posting Permissions

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