Import a MS Excel spread (2 Viewers)

Dick7Access

Dick S
Local time
Today, 02:05
Joined
Jun 9, 2009
Messages
3,996
How to import a MS Excel spread sheet Definition. Can’t find anything on Google.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:05
Joined
May 7, 2009
Messages
14,982
what is spreadsheet Definition?
column structure and type?
 

Dick7Access

Dick S
Local time
Today, 02:05
Joined
Jun 9, 2009
Messages
3,996
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, 15:05
Joined
May 7, 2009
Messages
14,982
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

Registered User.
Local time
Yesterday, 23:05
Joined
Sep 22, 2014
Messages
625
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, 02:05
Joined
Jun 9, 2009
Messages
3,996
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, 07:05
Joined
Sep 21, 2011
Messages
9,550
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, 02:05
Joined
Jun 9, 2009
Messages
3,996
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, 07:05
Joined
Sep 21, 2011
Messages
9,550
You do this once, then reap the benefits adinfinitum?
 

Dick7Access

Dick S
Local time
Today, 02:05
Joined
Jun 9, 2009
Messages
3,996
I know nobody is going to believe this.
 

Attachments

  • Milage.zip
    9.1 KB · Views: 7

Dick7Access

Dick S
Local time
Today, 02:05
Joined
Jun 9, 2009
Messages
3,996
oops sent wrong file
 

Attachments

  • MilageTmplate.zip
    9.7 KB · Views: 8

Gasman

Enthusiastic Amateur
Local time
Today, 07:05
Joined
Sep 21, 2011
Messages
9,550
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, 02:05
Joined
Jun 9, 2009
Messages
3,996
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, 07:05
Joined
Sep 21, 2011
Messages
9,550
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, 02:05
Joined
Jun 9, 2009
Messages
3,996
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.
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:05
Joined
Mar 14, 2017
Messages
6,062
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, 07:05
Joined
Sep 21, 2011
Messages
9,550
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:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:05
Joined
Feb 19, 2002
Messages
34,345
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