Board index » Excel Programming

All times are UTC



 import into excel from flat file 
Author Message
vanessa demo
#1 / 8
 import into excel from flat file

Hi,

I want to import a flat file in excel:

the file looks like this:  every letter must be imported into a separate
cell.

D A B G B B G H
D A B E B I G H
D A B E B D G H
D A A I A A G H
D A B K B H G H
D A B I B J G H
D A B C A I G H
D A A I H G G H
D A A C H I G H
D A A C H J G H
D A A K I D G H
D A A I J H G H
D A A G A B G H

I want to import starting from
A2  ......H2
A3 ...... H3
A4 .......H4

...

can this be done using VB and a macro?

Thanks



Fri, 25 Nov 2005 07:12:01 GMT  
vanessa demo
#2 / 8
 import into excel from flat file
the flat file is tab separated....


Quote:
> Hi,

> I want to import a flat file in excel:

> the file looks like this:  every letter must be imported into a separate
> cell.

> D A B G B B G H
> D A B E B I G H
> D A B E B D G H
> D A A I A A G H
> D A B K B H G H
> D A B I B J G H
> D A B C A I G H
> D A A I H G G H
> D A A C H I G H
> D A A C H J G H
> D A A K I D G H
> D A A I J H G H
> D A A G A B G H

> I want to import starting from
> A2  ......H2
> A3 ...... H3
> A4 .......H4

> ...

> can this be done using VB and a macro?

> Thanks



Fri, 25 Nov 2005 07:25:36 GMT  
Patrick Mollo
#3 / 8
 import into excel from flat file
There are several ways to achieve this.
here are some:
(1) use the Workbooks.OpenText method..
start the macro recorder, then open the file, follow the
wizard. stop the recorder & take a look at the array in
the code that the recorder created for you. The follwoing
code was created this way - I added one line and changed
one line, to enable the use to select a file...

Sub SupOpenTABFile()
    Dim FN As String
    FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
    If FN = "False" Then Exit Sub ' user cancelled
    Workbooks.OpenText Filename:=FN, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array
(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True
End Sub

(2) Use th eFile "Open" for Input method - not as
efficient as using a FileSystemObject, but its relatively
fast and easier to understand...
Sub SupOpenFile()
    Dim FN As String
    FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
    If FN = "False" Then Exit Sub ' user cancelled
    Dim rw As Long, cl As Long, text As String
    Dim  ar As Variant
    Open FN For Input As #3
    Do While Not EOF(3)
        Input #3, text
        ar = Split(text, Chr(9))
        rw = rw + 1
        Range(Cells(rw, 1), Cells(rw, UBound(ar, 1))) = ar
    Loop
    Close

End Sub

HTH
Patrick Molloy
Microsoft Excel MVP

Quote:
>-----Original Message-----
>the flat file is tab separated....



>> Hi,

>> I want to import a flat file in excel:

>> the file looks like this:  every letter must be

imported into a separate
Quote:
>> cell.

>> D A B G B B G H
>> D A B E B I G H
>> D A B E B D G H
>> D A A I A A G H
>> D A B K B H G H
>> D A B I B J G H
>> D A B C A I G H
>> D A A I H G G H
>> D A A C H I G H
>> D A A C H J G H
>> D A A K I D G H
>> D A A I J H G H
>> D A A G A B G H

>> I want to import starting from
>> A2  ......H2
>> A3 ...... H3
>> A4 .......H4

>> ...

>> can this be done using VB and a macro?

>> Thanks

>.



Fri, 25 Nov 2005 07:52:23 GMT  
vanessa demo
#4 / 8
 import into excel from flat file
Hi Patrick,

Thanks

but... I really want to import these values in an existing worksheet.
(containing a lot of formulas)
is this also possible?

Thanks
Vanessa


Quote:
> There are several ways to achieve this.
> here are some:
> (1) use the Workbooks.OpenText method..
> start the macro recorder, then open the file, follow the
> wizard. stop the recorder & take a look at the array in
> the code that the recorder created for you. The follwoing
> code was created this way - I added one line and changed
> one line, to enable the use to select a file...

> Sub SupOpenTABFile()
>     Dim FN As String
>     FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
>     If FN = "False" Then Exit Sub ' user cancelled
>     Workbooks.OpenText Filename:=FN, Origin:=xlMSDOS, _
>         StartRow:=1, DataType:=xlDelimited,
> TextQualifier:=xlDoubleQuote, _
>         ConsecutiveDelimiter:=False, Tab:=True,
> Semicolon:=False, Comma:=False _
>         , Space:=False, Other:=False, FieldInfo:=Array
> (Array(1, 1), Array(2, 1), _
>         Array(3, 1), Array(4, 1), Array(5, 1)),
> TrailingMinusNumbers:=True
> End Sub

> (2) Use th eFile "Open" for Input method - not as
> efficient as using a FileSystemObject, but its relatively
> fast and easier to understand...
> Sub SupOpenFile()
>     Dim FN As String
>     FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
>     If FN = "False" Then Exit Sub ' user cancelled
>     Dim rw As Long, cl As Long, text As String
>     Dim  ar As Variant
>     Open FN For Input As #3
>     Do While Not EOF(3)
>         Input #3, text
>         ar = Split(text, Chr(9))
>         rw = rw + 1
>         Range(Cells(rw, 1), Cells(rw, UBound(ar, 1))) = ar
>     Loop
>     Close

> End Sub

> HTH
> Patrick Molloy
> Microsoft Excel MVP

> >-----Original Message-----
> >the flat file is tab separated....



> >> Hi,

> >> I want to import a flat file in excel:

> >> the file looks like this:  every letter must be
> imported into a separate
> >> cell.

> >> D A B G B B G H
> >> D A B E B I G H
> >> D A B E B D G H
> >> D A A I A A G H
> >> D A B K B H G H
> >> D A B I B J G H
> >> D A B C A I G H
> >> D A A I H G G H
> >> D A A C H I G H
> >> D A A C H J G H
> >> D A A K I D G H
> >> D A A I J H G H
> >> D A A G A B G H

> >> I want to import starting from
> >> A2  ......H2
> >> A3 ...... H3
> >> A4 .......H4

> >> ...

> >> can this be done using VB and a macro?

> >> Thanks

> >.



Fri, 25 Nov 2005 08:38:13 GMT  
Tom Ogilv
#5 / 8
 import into excel from flat file
http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic? for Applications

Regards,
Tom Ogilvy


Quote:
> Hi Patrick,

> Thanks

> but... I really want to import these values in an existing worksheet.
> (containing a lot of formulas)
> is this also possible?

> Thanks
> Vanessa



> > There are several ways to achieve this.
> > here are some:
> > (1) use the Workbooks.OpenText method..
> > start the macro recorder, then open the file, follow the
> > wizard. stop the recorder & take a look at the array in
> > the code that the recorder created for you. The follwoing
> > code was created this way - I added one line and changed
> > one line, to enable the use to select a file...

> > Sub SupOpenTABFile()
> >     Dim FN As String
> >     FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
> >     If FN = "False" Then Exit Sub ' user cancelled
> >     Workbooks.OpenText Filename:=FN, Origin:=xlMSDOS, _
> >         StartRow:=1, DataType:=xlDelimited,
> > TextQualifier:=xlDoubleQuote, _
> >         ConsecutiveDelimiter:=False, Tab:=True,
> > Semicolon:=False, Comma:=False _
> >         , Space:=False, Other:=False, FieldInfo:=Array
> > (Array(1, 1), Array(2, 1), _
> >         Array(3, 1), Array(4, 1), Array(5, 1)),
> > TrailingMinusNumbers:=True
> > End Sub

> > (2) Use th eFile "Open" for Input method - not as
> > efficient as using a FileSystemObject, but its relatively
> > fast and easier to understand...
> > Sub SupOpenFile()
> >     Dim FN As String
> >     FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
> >     If FN = "False" Then Exit Sub ' user cancelled
> >     Dim rw As Long, cl As Long, text As String
> >     Dim  ar As Variant
> >     Open FN For Input As #3
> >     Do While Not EOF(3)
> >         Input #3, text
> >         ar = Split(text, Chr(9))
> >         rw = rw + 1
> >         Range(Cells(rw, 1), Cells(rw, UBound(ar, 1))) = ar
> >     Loop
> >     Close

> > End Sub

> > HTH
> > Patrick Molloy
> > Microsoft Excel MVP

> > >-----Original Message-----
> > >the flat file is tab separated....



> > >> Hi,

> > >> I want to import a flat file in excel:

> > >> the file looks like this:  every letter must be
> > imported into a separate
> > >> cell.

> > >> D A B G B B G H
> > >> D A B E B I G H
> > >> D A B E B D G H
> > >> D A A I A A G H
> > >> D A B K B H G H
> > >> D A B I B J G H
> > >> D A B C A I G H
> > >> D A A I H G G H
> > >> D A A C H I G H
> > >> D A A C H J G H
> > >> D A A K I D G H
> > >> D A A I J H G H
> > >> D A A G A B G H

> > >> I want to import starting from
> > >> A2  ......H2
> > >> A3 ...... H3
> > >> A4 .......H4

> > >> ...

> > >> can this be done using VB and a macro?

> > >> Thanks

> > >.



Fri, 25 Nov 2005 12:58:20 GMT  
Dave Peterso
#6 / 8
 import into excel from flat file
Another option if you're using xl2k or higher is to do the recording that
Patrick suggested, but instead of starting with file|Open, start with
Data|Import External Data.

You can import starting at a specific cell.

Quote:

> Hi Patrick,

> Thanks

> but... I really want to import these values in an existing worksheet.
> (containing a lot of formulas)
> is this also possible?

> Thanks
> Vanessa



> > There are several ways to achieve this.
> > here are some:
> > (1) use the Workbooks.OpenText method..
> > start the macro recorder, then open the file, follow the
> > wizard. stop the recorder & take a look at the array in
> > the code that the recorder created for you. The follwoing
> > code was created this way - I added one line and changed
> > one line, to enable the use to select a file...

> > Sub SupOpenTABFile()
> >     Dim FN As String
> >     FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
> >     If FN = "False" Then Exit Sub ' user cancelled
> >     Workbooks.OpenText Filename:=FN, Origin:=xlMSDOS, _
> >         StartRow:=1, DataType:=xlDelimited,
> > TextQualifier:=xlDoubleQuote, _
> >         ConsecutiveDelimiter:=False, Tab:=True,
> > Semicolon:=False, Comma:=False _
> >         , Space:=False, Other:=False, FieldInfo:=Array
> > (Array(1, 1), Array(2, 1), _
> >         Array(3, 1), Array(4, 1), Array(5, 1)),
> > TrailingMinusNumbers:=True
> > End Sub

> > (2) Use th eFile "Open" for Input method - not as
> > efficient as using a FileSystemObject, but its relatively
> > fast and easier to understand...
> > Sub SupOpenFile()
> >     Dim FN As String
> >     FN = Application.GetOpenFilename("TEXT (*.txt),*.txt")
> >     If FN = "False" Then Exit Sub ' user cancelled
> >     Dim rw As Long, cl As Long, text As String
> >     Dim  ar As Variant
> >     Open FN For Input As #3
> >     Do While Not EOF(3)
> >         Input #3, text
> >         ar = Split(text, Chr(9))
> >         rw = rw + 1
> >         Range(Cells(rw, 1), Cells(rw, UBound(ar, 1))) = ar
> >     Loop
> >     Close

> > End Sub

> > HTH
> > Patrick Molloy
> > Microsoft Excel MVP

> > >-----Original Message-----
> > >the flat file is tab separated....



> > >> Hi,

> > >> I want to import a flat file in excel:

> > >> the file looks like this:  every letter must be
> > imported into a separate
> > >> cell.

> > >> D A B G B B G H
> > >> D A B E B I G H
> > >> D A B E B D G H
> > >> D A A I A A G H
> > >> D A B K B H G H
> > >> D A B I B J G H
> > >> D A B C A I G H
> > >> D A A I H G G H
> > >> D A A C H I G H
> > >> D A A C H J G H
> > >> D A A K I D G H
> > >> D A A I J H G H
> > >> D A A G A B G H

> > >> I want to import starting from
> > >> A2  ......H2
> > >> A3 ...... H3
> > >> A4 .......H4

> > >> ...

> > >> can this be done using VB and a macro?

> > >> Thanks

> > >.

--

Dave Peterson



Sat, 26 Nov 2005 01:58:33 GMT  
memyself&
#7 / 8
 import into excel from flat file



Quote:
> Hi,

> I want to import a flat file in excel:

> the file looks like this:  every letter must be imported into a separate
> cell.

> D A B G B B G H
> D A B E B I G H
> D A B E B D G H
> D A A I A A G H
> D A B K B H G H
> D A B I B J G H
> D A B C A I G H
> D A A I H G G H
> D A A C H I G H
> D A A C H J G H
> D A A K I D G H
> D A A I J H G H
> D A A G A B G H

> I want to import starting from
> A2  ......H2
> A3 ...... H3
> A4 .......H4

> ...

> can this be done using VB and a macro?

> Thanks

Hi Vanessa.

How about rying this. It's free cardware. Should do the trick.

Find it with google.
CSVdb 4.1.1

Wil

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.488 / Virus Database: 287 - Release Date: 5-6-03



Sat, 26 Nov 2005 09:34:03 GMT  
Peter Russe
#8 / 8
 import into excel from flat file

Quote:
>  It's free cardware.

This is a new term to me.
What is Cardware?

The definitions I can find on the web don't seem to make sense in this
context.

Regards

Peter Russell



Sat, 26 Nov 2005 10:53:00 GMT  
 
 Page 1 of 1
  [ 8 post ] 

 Relevant Pages 

1. Import Emails from flat text file

2. Has Anyone Imported Flat Files From HP3000?

3. Import MSAccess Table From Flat FIle

4. Import Flat Text File

5. Help! Importing a Flat File

6. Decomposing flat file during import, or other data migration strategies

7. Scrubbing flat file data pre-import

8. HELP IMPORT FLAT TXT FILE INTO ACCESS 97!

9. importing flat files

10. importing data from flat files

 
Powered by phpBB® Forum Software © phpBB Group