No announcement yet.

Importing: Comma Delineated template

  • Filter
  • Time
  • Show
Clear All
new posts

  • Importing: Comma Delineated template

    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!

  • #2
    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...



    • #3
      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:


      • #4
        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.