Import a MS Excel spread

[Dick7Access]

How to import a MS Excel spread sheet Definition. Can’t find anything on Google.

 

[arnelgp]

what is spreadsheet Definition?
column structure and type?

 

[Dick7Access]

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]

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]

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]

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]

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]

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]

You do this once, then reap the benefits adinfinitum?

 

[Dick7Access]

I know nobody is going to believe this.

 

[Dick7Access]

oops sent wrong file

 

[Gasman]

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]

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]

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.

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]

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.

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]

Just used my new template for my first tax deductions of 2022. Worked great. Thanks all

 

[Isaac]

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]

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.

 

[Isaac]

I may have to give them a second try!

 

[Pat Hartman]

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.