Unable to load .data file in excel using VBA

[Dani_LobP]

Hello,

I'm trying to build a dialog box where will ask me to load a .data file extension file which later on i will edit few things and save as .xlsx.

Problem is that whenever the file is selected and supposed to be loaded, there is no outcome... it simply ends macro as if nothing happened.
If i try go step by step, it will open the box, i choose the file, and then, empty ... its like if for some reason the data in the .data file is not loaded.

basically the reason for open .data is so i can then edit in excel and turn data which will appear all in first column into a table by using the text into columns tool.

Anyone knows why this .data file might not be loading? My VBA knowledge is very basic and its my first time working with dialog box option..

To test i also tried to just record a macro and do all manually (open the file, edit it, close it and all) and when check macro step by step, it will not show data but keep going with everything else...

Not sure if it makes sense but hopefully someone can help.

Thanks in advance.

 

[Special-K99]

.CSV is a comma separated file
.TXT is a text file
.PDF is a Portable Document Format file

What's a .data file ?
Where did you get the file from?

Excel needs to know what type of file .data is, some extensions it will recognise

 

[Norie]

Can you post your code?

 

[Dani_LobP]

.CSV is a comma separated file
.TXT is a text file
.PDF is a Portable Document Format file

What's a .data file ?
Where did you get the file from?

Excel needs to know what type of file .data is, some extensions it will recognise

.data is a DATA file, probably generated by some reporting tool.
I just open Excel, and drag the .data file into it, and excel will read it, but will read it as text and have it all in 1 column, that's why then i just "text to columns" and make some edits.

Wanted to try make some macro where i can load that .data file and make the changes needed and then be saved as .xlsx. Currently i do this manually and thought it could work with a macro and save some time.

That's why i'm not sure why by using the dialogbox won't recognize it or load it.

 

[Dani_LobP]

Sub Get_Data()
Dim filetoopen As Variant


filetoopen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.data (*.data),")
''
Workbooks.Open fileName:=filetoopen


End Sub

in theory this should prompt the folder and then i chose the file and then appear on excel.
it lets me choose file, but then it just opens empty sheet.

as i said its my first time using dialog box to prompt for a file to be opened and i guess rest of code i add should just be continued after that...

 

[Norie]

Can you manually open a .data file from Excel?

 

[Dani_LobP]

yes, and straight ahead will appear the box dialog to format it, and i just select delimited, choose separator and some other stuff and when finish it will display data as i wish.

i tried closing all and restarting excel and for some reason seems to be working now.

not sure why it wouldn't work before.

 

[Norie]

So it's a text file?

If that's the case you probably need to use Workbooks.OpenText rather than Workbooks.Open.