How to import file specs from access to excel
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
How to import file specs from access to excel
28 million results in Google.
Zero real world solution (that I could find)
I would like to import a file spec from Access into Excel
I cannot figure out how.
Thank you for any direction you have for me.
Michael
Zero real world solution (that I could find)
I would like to import a file spec from Access into Excel
I cannot figure out how.
Thank you for any direction you have for me.
Michael
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to import file specs from access to excel
Can you explain what you mean by "importing a file spec"?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
I have a text file which is fixed width ( about 50+ fields)
The first time I import the file into Access I create a file spec using the specs supplied by the file creator.
I save it in Access as an Import specification so I can use it to import the text file in the future.
We are moving away from Access, so I need a way to import the text files into Excel without having to re-invent the wheel.
I want to be able to import the text file into Excel without having to rewrite the Import Specs.
Thank you !
Michael
The first time I import the file into Access I create a file spec using the specs supplied by the file creator.
I save it in Access as an Import specification so I can use it to import the text file in the future.
We are moving away from Access, so I need a way to import the text files into Excel without having to re-invent the wheel.
I want to be able to import the text file into Excel without having to rewrite the Import Specs.
Thank you !
Michael
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to import file specs from access to excel
Access stores its import and export specifications in two hidden system tables:
MSysIMEXSpecs lists the specifications with their general settings.
MSysIMEXColumns lists the settings for each column of each specification.
In theory, you can export these two tables to Excel, but unfortunately, Excel wouldn't know what to do with them.
You might use them as documentation, but basically, you'll have to reinvent the wheel...
P.S. Why are you moving from Access to Excel?
MSysIMEXSpecs lists the specifications with their general settings.
MSysIMEXColumns lists the settings for each column of each specification.
In theory, you can export these two tables to Excel, but unfortunately, Excel wouldn't know what to do with them.
You might use them as documentation, but basically, you'll have to reinvent the wheel...

P.S. Why are you moving from Access to Excel?

Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
I am the only person in my division that has used Access since I started in 1998.Why are you moving from Access to Excel?
Our IT department stopped supporting Access many years ago, and basically depend on me to handle it on my own.
Are you ready for this?

I am retiring in about a year and they are not "backfilling" my position.
The suits figured since "everyone" knows how to use Excel, they want to convert all databases (1,000s+) by the time I leave.
Crazy.... but true story.
Michael
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to import file specs from access to excel
Ouch!
That's a fine mess they have gotten themselves into!

Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 742
- Joined: 27 Jun 2021, 10:46
Re: How to import file specs from access to excel
They'll almost certainly live to regret that!
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
Michael Abrams wrote: ↑03 Aug 2022, 15:01I am the only person in my division that has used Access since I started in 1998.Why are you moving from Access to Excel?
Our IT department stopped supporting Access many years ago, and basically depend on me to handle it on my own.
Are you ready for this?![]()
I am retiring in about a year and they are not "backfilling" my position.
The suits figured since "everyone" knows how to use Excel, they want to convert all databases (1,000s+) by the time I leave.
Crazy.... but true story.
Michael
Wow - 15 years later and I am still here. But I WILL retire next year, for sure

So - is there still no way to export file layout specs from Access to Excel? (asking for a friend

Michael Abrams
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to import file specs from access to excel
The answer is still the same.
(It's 17 months since you started this thread...)
(It's 17 months since you started this thread...)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 678
- Joined: 14 Nov 2012, 16:06
Re: How to import file specs from access to excel
Did you try to open these text files (csv, txt ?) plainly in Excel ? How do you assess the results ?
How many different importspecifications do you need ?
Use webqueries in Excel to store importspecifications or Use powerquery.
How many different importspecifications do you need ?
Use webqueries in Excel to store importspecifications or Use powerquery.
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
Yeah - I have tried to open the text file in Excel and it still asks for text file import specs.
All of the specs I have are 100+ fields Too much manual work

I'll let the suits figure this out. Thanks snb!
And HansV - I was looking at my join date. Still, 17 months is a while to wait to ask for any new possibilities

-
- 5StarLounger
- Posts: 678
- Joined: 14 Nov 2012, 16:06
Re: How to import file specs from access to excel
Not if you useYeah - I have tried to open the text file in Excel and it still asks for text file import specs.
Code: Select all
Sub M_snb()
Workbooks.Open "G:\OF\adressen.csv"
End Sub
Code: Select all
Sub M_snb()
sheets.add , sheets(sheets.count),, "G:\OF\adressen.csv"
End Sub
Code: Select all
Sub M_snb()
Workbooks.OpenText "G:\OF\adressen.csv"
End Sub
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
I am trying to import a .txt file - not a csv file and I am not sure how I would use the code.
If I import a fixed width text file into Excel, how does Excel know what the fixed widths are? In Access, I created specs for import.
The suits will have to figure this out. So far, they haven't, so we still have Access for a bit longer
Thank you!
Michael
If I import a fixed width text file into Excel, how does Excel know what the fixed widths are? In Access, I created specs for import.
The suits will have to figure this out. So far, they haven't, so we still have Access for a bit longer

Thank you!
Michael
-
- 5StarLounger
- Posts: 678
- Joined: 14 Nov 2012, 16:06
Re: How to import file specs from access to excel
You can test the code by replacing "G:\OF\adressen.csv" by the fullname of your file and run it.
The same applies to the following code: (the file "G:\OF\example.txt")
The library tries to figure out which structure the file has.
The same applies to the following code: (the file "G:\OF\example.txt")
The library tries to figure out which structure the file has.
Code: Select all
Sub M_snb()
With CreateObject("ADODB.Recordset")
.Open "SELECT * FROM `example.txt`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\"
sheets(1).cells(1).copyfromrecordset .datasource
End With
End Sub
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
Thank you - I'll take a stab at it 

-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
Assuming:
Filename: Test_BC_Import
Path: C:\Downloads
Workbook name: ImportTest_mra
place this code where?
Sorry for going on with this. I do appreciate your patience.
Michael
Filename: Test_BC_Import
Path: C:\Downloads
Workbook name: ImportTest_mra
place this code where?
Code: Select all
Sub M_snb()
With CreateObject("ADODB.Recordset")
.Open "SELECT * FROM `TestBCImport.txt`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Downloads\"
sheets(1).cells(1).copyfromrecordset .datasource
End With
End Sub
Michael
-
- Administrator
- Posts: 80088
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to import file specs from access to excel
The code will import into the active sheet.
Shouldn't TestBCImport.txt be Test_BC_Import.txt ?
Shouldn't TestBCImport.txt be Test_BC_Import.txt ?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 678
- Joined: 14 Nov 2012, 16:06
Re: How to import file specs from access to excel
Open the attachment to this post.
Open VBA: Alt-F11
Put the cursor in the code: eithe in sheet1 or in ThisWorkbook
Run the code F5
Open VBA: Alt-F11
Put the cursor in the code: eithe in sheet1 or in ThisWorkbook
Run the code F5
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 597
- Joined: 10 Feb 2010, 17:32
Re: How to import file specs from access to excel
OK - this is promising. You're awesome ! My company has disabled the macros, so I am going to recreate the macros in my own workbook. Do I need to save it as xlsb - what about xlsm - what about xlsx ?
And you're correct about the Test_BC_Import.txt
I will get on this in the morning.
Michael
Thank you SO much again.
Michael
And you're correct about the Test_BC_Import.txt
I will get on this in the morning.
Michael
Thank you SO much again.
Michael