VBA Command to Open Excel Template

[JuneTwentyFourteen]

Apologies if this has been answered before, but I am unable to find an existing thread.

I am trying to write a VBA macro whereby I can press a command button to open another excel workbook template that I have set up separately.

Any ideas?

Colin

[Norie]

Colin

Do you want to open the template or do you want to create a new workbook based off the template?

[JuneTwentyFourteen]

Colin

Do you want to open the template or do you want to create a new workbook based off the template?

Hi Norie

The intention is to open an existing template upon pressing the command button.
I will have a main workbook with 20 or so sheets containing different data.
On 1 of the sheets, I intend to have a command button that opens a new workbook with a template set up. I would then like some of the data to be transferred into that template in a condensed form in order to share with other users. But for now it is just the macro to open the existing file that I am looking for.

Regards

Colin

[Norie]

All you need is Workbooks.Open then.

Please Login or Register  to view this content.

[JuneTwentyFourteen]

Thanks Norie

I am receiving an error message against the strFilename string;

Compile Error
Expected Sub, Function or Property

What does that mean and is there a way that I can debug it?

Colin

[Parth007]

Hi Colin,


May be a Typo error... "=" sign is missing there.. see below code for refrence..

Please Login or Register  to view this content.

[Norie]

It's a typo, I've fixed it.

[JuneTwentyFourteen]

I am going wrong somewhere, I just can't see it.
I have put the following code into VBA (I have more or less copied verbatim)

Please Login or Register  to view this content.

and I am now receiving the following error message:

"Runtime error 1004
Sorry we could not find
C:\Users\Colin\Desktop\DeliveryNotesDevelopment\DeliveryNote.xlsx
Is it possible it was moved, removed or deleted?"

I am certain that I have entered the path and file name correctly.

[Parth007]

Hi Colin,

Just checked with the code you posted, yes the file name you wanted to open does not exist in the Folder you specified...

[JuneTwentyFourteen]

Hi Colin,

Just checked with the code you posted, yes the file name you wanted to open does not exist in the Folder you specified...

I am getting closer, I have got the code provided to open an new workbook which I have named "Book1" - just a blank worksheet and it works! But for some reason, my target worksheet template "DeliveryNote" doesn't open despite the path and filename definitely being correct!
Could there be a setting or a bug in the target worksheet that is preventing the macro from working?

[Parth007]

Hi Colin,

If you trying to open a Template then the extension of the file would be either
(FILENAME.xltm or FILENAME.xlt)

DeliveryNote.xlsx dont seem to be a Excel Template file.. Please verify

[JuneTwentyFourteen]

Hi Colin,

If you trying to open a Template then the extension of the file would be either
(FILENAME.xltm or FILENAME.xlt)

DeliveryNote.xlsx dont seem to be a Excel Template file.. Please verify

Parth, its actually saved as xlsm have changed the filename to that and....... it WORKS!!

Thanks very much for your time and patience, most appreciated!

[Parth007]

Hi Colin, PLeas mark the trhead as solved