Import a MS Excel spread (1 Viewer)

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
How to import a MS Excel spread sheet Definition. Can’t find anything on Google.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:52
Joined
May 7, 2009
Messages
19,169
what is spreadsheet Definition?
column structure and type?
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
what is spreadsheet Definition?
column structure and type?
I often import a DB in access with just the forms, tables so on but without the data. Its call definition only I am assuming Excel has the same thing. I have a few spread sheets that I use every year. Instead of building a new spread sheet or copying last years and save the time of putting in all the headers and code, I was assuming I could import last years without data.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:52
Joined
May 7, 2009
Messages
19,169
i am not sure if there is same process of just
importing the Formats from your spreadsheet.
what you can do is make a "template" workbook.
remove all data from previous workbooks and save
it to another name (meaningful to its purpose).
when you need same format, use the template and
save it later in a different name.
 

oleronesoftwares

Passionate Learner
Local time
Today, 05:52
Joined
Sep 22, 2014
Messages
1,159
Hi,
Ms excel does not have data structures defined in the columns as compared to databases.

E.g in a database you can define in a table the type of data to be stored in a field, and this will be enforced at the form level
What excel does is to allow the definition of data to be stored at the point of data entry, e.g formating an excel column as date, or currency, and this can easily be overwritten.

This is the reason when importing from excel, the option to import data definition only is not available.

@arnelgp suggestion is a good way to go.
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
Thanks to all. I guess I will have to bite the bullet. When I went to set up my spread sheets for 2022 I said this is the third year I have forgotten to make a template. I will have to start from scratch. Even now I only have a few days to get my SS for mileage for tax purposes ready to travel. I will endeavor to save it first as a template. I will let you know next year at this time if I was successful. In the meantime maybe somebody at MS is listening.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:52
Joined
Sep 21, 2011
Messages
14,041
Hardly? :(
You save your existing 2021 file as an Excel template (xltx)
Then you clear this years data. Then you save it again. And close it.
Then you click New, My templates and select your template.

That is it, a few minutes work. The deletion of data will probably take you longer? :)
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
Hardly? :(
You save your existing 2021 file as an Excel template (xltx)
Then you clear this years data. Then you save it again. And close it.
Then you click New, My templates and select your template.

That is it, a few minutes work. The deletion of data will probably take you longer? :)
10 times longer. Why is it we never have time to do something right away, but have time to do itn10 times longer?
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:52
Joined
Sep 21, 2011
Messages
14,041
You do this once, then reap the benefits adinfinitum?
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
I know nobody is going to believe this.
 

Attachments

  • Milage.zip
    9.1 KB · Views: 244

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
oops sent wrong file
 

Attachments

  • MilageTmplate.zip
    9.7 KB · Views: 232

Gasman

Enthusiastic Amateur
Local time
Today, 12:52
Joined
Sep 21, 2011
Messages
14,041
You have not cleared all the relevant cells?
You also have not saved it as an Excel template? The option is in the dropdown for file types.
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
The four lines of data I left in so I could refresh my memory on the formulars, as I only use this SS one a year, and though it would not take too long to remove four lines of data. I have saved it to a folder called Templates. I did not know about the excel template option until you advised me. What is the advantage of the template option as opposed to the way I did it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:52
Joined
Sep 21, 2011
Messages
14,041
The four lines of data I left in so I could refresh my memory on the formulars, as I only use this SS one a year, and though it would not take too long to remove four lines of data. I have saved it to a folder called Templates. I did not know about the excel template option until you advised me. What is the advantage of the template option as opposed to the way I did it.
That fact that you use an actual template. :)
So every time you want a new workbook based on that template, you get a copy automatically, no chance of overwriting the actual template, which you can do your way? The fact that you save it as a Template saves it to the common Templates folder, so you can find it via the GUI.

No guarantee you have the correct Templates folder?

I actually have to do this every month for my community work, but I just created some code, as I also want to copy that month's data to a master workbook, to keep track of all trips. Otherwise a template for that would be a good idea.

Columns with formulae I leave alone.
Code:
Sub ClearGCD()
' Clear trips sheet for next month
Dim iTotalsRow As Integer
'First find the Totals Row
Range("B1").Select
Columns("B:B").Select
Selection.Find(What:="totals", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
iTotalsRow = ActiveCell.Row - 1

ActiveSheet.Range("A4:E" & iTotalsRow).ClearContents
ActiveSheet.Range("G4:G" & iTotalsRow).ClearContents
ActiveSheet.Range("J4:J" & iTotalsRow).ClearContents

End Sub
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
That fact that you use an actual template. :)
So every time you want a new workbook based on that template, you get a copy automatically, no chance of overwriting the actual template, which you can do your way? The fact that you save it as a Template saves it to the common Templates folder, so you can find it via the GUI.

No guarantee you have the correct Templates folder?

I actually have to do this every month for my community work, but I just created some code, as I also want to copy that month's data to a master workbook, to keep track of all trips. Otherwise a template for that would be a good idea.

Columns with formulae I leave alone.
Code:
Sub ClearGCD()
' Clear trips sheet for next month
Dim iTotalsRow As Integer
'First find the Totals Row
Range("B1").Select
Columns("B:B").Select
Selection.Find(What:="totals", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
iTotalsRow = ActiveCell.Row - 1

ActiveSheet.Range("A4:E" & iTotalsRow).ClearContents
ActiveSheet.Range("G4:G" & iTotalsRow).ClearContents
ActiveSheet.Range("J4:J" & iTotalsRow).ClearContents

End Sub
Thanks, that is a big advantage. I am going to do it.
 

Dick7Access

Dick S
Local time
Today, 08:52
Joined
Jun 9, 2009
Messages
4,197
Just used my new template for my first tax deductions of 2022. Worked great. Thanks all
 

Isaac

Lifelong Learner
Local time
Today, 05:52
Joined
Mar 14, 2017
Messages
8,738
The four lines of data I left in so I could refresh my memory on the formulars, as I only use this SS one a year, and though it would not take too long to remove four lines of data. I have saved it to a folder called Templates. I did not know about the excel template option until you advised me. What is the advantage of the template option as opposed to the way I did it.

I never use them because of that. They have no advantage that I know, other than, you double click the template and a copy of it opens instead of it. That's it.

I guess if you are in an environment where you actually risk giving users the ONLY original you have, then.....but who in the world would do that???

To me, what I listed as a possible advantage is actually an annoyance I can't stand, and have never used them in any office program except for Outlook, where you can use VBA to open a saved message file as a template, then manipulate it further for sending, which is handy.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:52
Joined
Sep 21, 2011
Messages
14,041
Consistency of files in one place, unless you want to do more work?
Very little chance of overwriting unless deliberate.
No drawbacks to using them rather than not, that I have noticed.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 05:52
Joined
Mar 14, 2017
Messages
8,738
I may have to give them a second try!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,970
You know how to use Access. Why would you not make a database to do this? You might even be able to find an Expenses template.
 

Users who are viewing this thread

Top Bottom