importing ascii file into excel using vba

[flix]

i am working on a spreadsheet, where a user should be able to import some data from an ascii, comma delimted file into excel. this was quite easily done using the record macro function, but now i would like to make it user friendly by giving the user the opportunity to pick the file they want to open.
if anybody knows any examples/tutorials/guides or knows what code is needed, any help is appreciated.
cheers

 

[TommyGun]

This will get the file loaded into Excel, and then you can process it further from there...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetFile()

<SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

f = Application.GetOpenFilename("All Files (*.*),*.*")

<SPAN style="color:#00007F">If</SPAN> f <> <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>

    Workbooks.<SPAN style="color:#00007F">Open</SPAN> FileName:=f

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[flix]

just tried that code out and it works nicely, cheers!!

next questions is, that as it is an ascii file, when it opens it opens it in a new excel window, is there any way to put it in the same window, just as a new sheet??

also, previously i had to specifiy that it was comma delimited (seperated) file when opening it, but i seem to bypass this with the code. is there anything i need to do about that or should it work fine automatically??

just checked the file that it opens, and the comma seperated data seems to have been inserted into one column, rather then each into its own column..what i have used to import the files sofar is this code:

    Workbooks.OpenText Filename:=sFileName, Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
       , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True

is there any way i could combine the two??

Edit#3
Done
seems to have worked it out itself, now just wondering on the first part (red)

 

[TommyGun]

So you want the new file imported into an already open file on a new sheet?

 

[flix]

yep, i was/am trying out something with dynamic ranges at the mo, but that would be a workaround as it would literally just copy all the info from the opened worksheet adn then close it...if it can be done in one step the coding would be nicer methinks...

 

[TommyGun]

Try this, it moves the spreadsheet to the end of the active workbook.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetFile()

<SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> w <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

<SPAN style="color:#00007F">Set</SPAN> a = ActiveWorkbook

f = Application.GetOpenFilename("All Files (*.*),*.*")

<SPAN style="color:#00007F">If</SPAN> f <> <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> w = Workbooks.Open(FileName:=f)
    w.Sheets(1).Copy After:=a.Sheets(a.Sheets.Count)
    w.<SPAN style="color:#00007F">Close</SPAN> SaveChanges:=<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> w = <SPAN style="color:#00007F">Nothing</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">Set</SPAN> a = <SPAN style="color:#00007F">Nothing</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[flix]

thank you very much...that is now pretty much all i wanted to get done...now for some more minor editing...

cheers tommy

note: will probably run into some more problems so will probably come back for more queries

 

[flix]

heres the next issue i have...just after opening it i have put this in to rename it to something useful (rename the sheet) and then copy it across...

    ActiveWorkbook.Sheets(1).Name = "data"
    ActiveWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

now what i really was hoping i would be able to do is to close the now inactive and useless workbook, so here are my attempts:
i thought easiest would be to activate the workboook and then use the close command, and supress the save as dialogue, as in:

Workbooks ("nameofworkbook").Activate
ActiveWorkbook.Close SaveChanges:= False

but as the name of the workboook is a variable, would i still be able to use this method?

 

[TommyGun]

Use the edited code from above. It should work for you.

 

[flix]

Option Explicit
Dim f As Variant
Dim a As Workbook
Dim w As Workbook

Sub GetFile()

Set a = ActiveWorkbook

'   User can choose the .asc file to use
f = Application.GetOpenFilename("All Files (*.asc),*.asc")

'   Open & import data and rename sheet
If f <> False Then
     Workbooks.OpenText Filename:=f, Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
       , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True

    Set w = Workbooks(f)
    w.Activate

    ActiveWorkbook.Sheets(1).Name = "data"

'   Copy data into main sheet & close other sheet
        w.Sheets(1).Copy After:=a.Sheets(a.Sheets.Count)
        w.Close SaveChanges:=False
        Set w = Nothing
End If

Set a = Nothing
        
End Sub

arg, slight problem with that method as the opening of the file is using the opentext function, as i need it to be comma delimited, and when i tried putting it in the same way as your code i got an error.

tried a little workaround as seen, but that didnt seem to like it either...