Import dat. file

[tuggers]

Could anybody PLEASE help!??

I am trying to import a lot of seperate dat files into a single worksheet, but every time i try and do this it opens up a seperate sheet for each file!!

Is there any way i can import all the files, and also continue to import as new ones are added

ANY help would be greatly appreciated

[Martin Fishlock]

Tuggers,

Excel opens a each file you open as a seperate worksheet.

You therefore have to copy the opened worksheet into the summary workbook.
This is quite easy to achive with VBA

The question is how do you sleect the files to import is it *.* or *.csv or
do you manually select them?


--
HTHs Martin


"tuggers" wrote:

>
> Could anybody PLEASE help!??
>
> I am trying to import a lot of seperate dat files into a single
> worksheet, but every time i try and do this it opens up a seperate
> sheet for each file!!
>
> Is there any way i can import all the files, and also continue to
> import as new ones are added
>
> ANY help would be greatly appreciated
>
>
> --
> tuggers
> ------------------------------------------------------------------------
> tuggers's Profile: http://www.excelforum.com/member.php...o&userid=29000
> View this thread: http://www.excelforum.com/showthread...hreadid=514838
>
>

[Tom Ogilvy]

did you do

Data=>Import External Data => Import Data, then select Text and select your
file - walk through the import wizard and designate a location?

If you need code, turn on the macro recorder while you do it manually and
then adjust the recorded code.

--
Regards,
Tom Ogilvy

"tuggers" <tuggers.23kukb_1140521701.8696@excelforum-nospam.com> wrote in
message news:tuggers.23kukb_1140521701.8696@excelforum-nospam.com...
>
> Could anybody PLEASE help!??
>
> I am trying to import a lot of seperate dat files into a single
> worksheet, but every time i try and do this it opens up a seperate
> sheet for each file!!
>
> Is there any way i can import all the files, and also continue to
> import as new ones are added
>
> ANY help would be greatly appreciated
>
>
> --
> tuggers
> ------------------------------------------------------------------------
> tuggers's Profile:
http://www.excelforum.com/member.php...o&userid=29000
> View this thread: http://www.excelforum.com/showthread...hreadid=514838
>

[Dave Peterson]

Ron de Bruin has sample code to merge a bunch of .csv files into one, then
import it.

Maybe you could modify it to work with your .dat files.

http://www.rondebruin.nl/csv.htm



tuggers wrote:
>
> Could anybody PLEASE help!??
>
> I am trying to import a lot of seperate dat files into a single
> worksheet, but every time i try and do this it opens up a seperate
> sheet for each file!!
>
> Is there any way i can import all the files, and also continue to
> import as new ones are added
>
> ANY help would be greatly appreciated
>
> --
> tuggers
> ------------------------------------------------------------------------
> tuggers's Profile: http://www.excelforum.com/member.php...o&userid=29000
> View this thread: http://www.excelforum.com/showthread...hreadid=514838

--

Dave Peterson

[tuggers]

Thanks for the interest guys

I have been trying to open them via the open option in the file menu on the menu bar. This seems to be the only way to import dat. files??
Then when i had selected them all and gone through the import text screens, each file is imported as a seperate worksheet.

If there is a better way, id be happy to try it.
Is anybody able to supply me with the required vba code to import the entire contents of the folder into a single worksheet?

I would greatly appreciate it.

[kounoike]

Hi

i don't know whether this would work in your case, but give it try.

Sub MultiImporttest()
Dim flname
Dim filename
Dim FileNum As Integer
Dim Counter As Long, maxrow As Long
Dim WorkResult As String
Dim ws As Worksheet
On Error GoTo ErrorCheck
maxrow = Cells.Rows.Count
'Ask for the name of the file.
filename = Application.GetOpenFilename(FileFilter:="all file (*.*),*.*",
MultiSelect:=True)
'Check for no entry.
If VarType(filename) = vbBoolean Then
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False

Counter = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For Each flname In filename
FileNum = FreeFile()
Open flname For Input As #FileNum
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & flname
Line Input #FileNum, WorkResult
Set ws = Nothing
Set ws = ActiveSheet
ws.Select
Cells(Counter, 1) = WorkResult
If WorkResult <> "" Then
Application.DisplayAlerts = False
Cells(Counter, 1).TextToColumns Destination:=Cells(Counter, 1),
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
End If
Counter = Counter + 1
If Counter > maxrow Then
MsgBox "data have over max rows: " & maxrow
Exit Sub
End If
Loop
'Close the open text file.
Close
Next

'Reset the application to its normal operating environment.
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrorCheck:
'Reset the application to its normal operating environment.
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "An error occured in the code."
End Sub

keizi

"tuggers" <tuggers.23mo2p_1140606603.494@excelforum-nospam.com> wrote in message
news:tuggers.23mo2p_1140606603.494@excelforum-nospam.com...
>
> Thanks for the interest guys
>
> I have been trying to open them via the open option in the file menu on
> the menu bar. This seems to be the only way to import dat. files??
> Then when i had selected them all and gone through the import text
> screens, each file is imported as a seperate worksheet.
>
> If there is a better way, id be happy to try it.
> Is anybody able to supply me with the required vba code to import the
> entire contents of the folder into a single worksheet?
>
> I would greatly appreciate it.
>
>
> --
> tuggers
> ------------------------------------------------------------------------
> tuggers's Profile:
http://www.excelforum.com/member.php...o&userid=29000
> View this thread: http://www.excelforum.com/showthread...hreadid=514838
>

[kounoike]

Sorry, I've forgot to add one if statement.

"kounoike" <kounoike@nowherembh.nifty.com> wrote in message
news:eMu1M$6NGHA.3272@tk2msftngp13.phx.gbl...
> Hi
>
> i don't know whether this would work in your case, but give it try.
>
> Sub MultiImporttest()
> Dim flname
> Dim filename
> Dim FileNum As Integer
> Dim Counter As Long, maxrow As Long
> Dim WorkResult As String
> Dim ws As Worksheet
> On Error GoTo ErrorCheck
> maxrow = Cells.Rows.Count
> 'Ask for the name of the file.
> filename = Application.GetOpenFilename(FileFilter:="all file (*.*),*.*",
> MultiSelect:=True)
> 'Check for no entry.
> If VarType(filename) = vbBoolean Then
> Exit Sub
> End If
>
> Application.ScreenUpdating = False
> Application.EnableEvents = False
>
> Counter = Cells(Cells.Rows.Count, "a").End(xlUp).Row

'===>>add a if statement below here
If Cells(Counter, "a") <> "" Then
Counter = Counter + 1
End If
'===>>end

> For Each flname In filename
> FileNum = FreeFile()
> Open flname For Input As #FileNum

keizi

[tuggers]

WOW!!

That looks fantastic!!

The only trouble is im a bit of a vba novice!

The required dat. files are stored in a folder called 'Tricoder'
and the file is stored on T: drive (shared drive at my place of work)
Can you please show me where i would place this information.

Again, many thanks for the help

[kounoike]

Hi

just run the macro, then a dialog for opening files appears. change to the folder
where files you want to import are, and select all files you want to import
- same way as you do when you use explore - and press OK button.
if you miss to select some files, run again the macro and select missing files
with worksheet where data already are selected, then it will add the data into
that worksheet.
That's all. But i'm not sure this will end up with what you want to get.

keizi

"tuggers" <tuggers.23otuq_1140707404.3643@excelforum-nospam.com> wrote in message
news:tuggers.23otuq_1140707404.3643@excelforum-nospam.com...
>
> WOW!!
>
> That looks fantastic!!
>
> The only trouble is im a bit of a vba novice!
>
> The required dat. files are stored in a folder called 'Tricoder'
> and the file is stored on T: drive (shared drive at my place of work)
> Can you please show me where i would place this information.
>
> Again, many thanks for the help
>
>
> --
> tuggers
> ------------------------------------------------------------------------
> tuggers's Profile:
http://www.excelforum.com/member.php...o&userid=29000
> View this thread: http://www.excelforum.com/showthread...hreadid=514838
>

[tuggers]

i tried using the code you supplied but its throwing up a syntax error for the following part:

filename = Application.GetOpenFilename(FileFilter:="all file (*.*),*.*",
MultiSelect:=True)

Any ideas of what to change to stop this??

[tuggers]

I have found the following information for importing files into a single worksheet.

The trouble is i dont really understand what it all means!!

Could somebody please fill in the necessary changes for this to work??

Many, many thanks

[kounoike]

Hi

i wrote the code in one line. but when i pasted the code, my mailer inserted
the code new line automatically that caused syntax error. i think there is
more places which cause syntax error.
i'll put the code changed which would not cause syntax error when you copy.
But in case that there are syntax error again, please let me know.

Sub MultiImporttest()
Dim flname
Dim filename
Dim FileNum As Integer
Dim Counter As Long, maxrow As Long
Dim WorkResult As String
Dim ws As Worksheet

On Error GoTo ErrorCheck
maxrow = Cells.Rows.Count
filename = Application.GetOpenFilename _
(FileFilter:="all file(*.*),*.*", MultiSelect:=True)
If VarType(filename) = vbBoolean Then
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False

Counter = Cells(Cells.Rows.Count, "a").End(xlUp).Row
If Cells(Counter, "a") <> "" Then
Counter = Counter + 1
End If

For Each flname In filename
FileNum = FreeFile()
Open flname For Input As #FileNum
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & flname
Line Input #FileNum, WorkResult
Set ws = Nothing
Set ws = ActiveSheet
ws.Select
Cells(Counter, 1) = WorkResult
If WorkResult <> "" Then
Application.DisplayAlerts = False
Cells(Counter, 1).TextToColumns _
Destination:=Cells(Counter, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, _
Other:=False
End If
Counter = Counter + 1
If Counter > maxrow Then
MsgBox "data have over max rows: " & maxrow
Exit Sub
End If
Loop
Close
Next

Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrorCheck:
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "An error occured in the code."
End Sub

keizi

"tuggers" <tuggers.23qoqz_1140794109.2564@excelforum-nospam.com> wrote in message
news:tuggers.23qoqz_1140794109.2564@excelforum-nospam.com...
>
> i tried using the code you supplied but its throwing up a syntax error
> for the following part:
>
> filename = Application.GetOpenFilename(FileFilter:="all file
> (*.*),*.*",
> MultiSelect:=True)
>
> Any ideas of what to change to stop this??
>
>
> --
> tuggers
> ------------------------------------------------------------------------
> tuggers's Profile:
http://www.excelforum.com/member.php...o&userid=29000
> View this thread: http://www.excelforum.com/showthread...hreadid=514838
>

[tuggers]

That seems to work!!

I cant thank you enough..........

So much appreciated