Free Microsoft Excel 2013 Quick Reference

VBA to open template

Hi

I have been trying to write VBA for Word, Excel and PowerPoint. We have a system where a user can open a document in EDMS (electronic database management system). The document opens in a template we've created. If the user wishes, they should be able to start a new document. This should open with the template.

I have written code in VBA in word and this all works fine and tested ok. But in excel pretty much the same syntax returns errors. The syntax for opening the document in word is:

Sub New_Document()

' New_Document Macro
' Replaces File | New

Documents.Add Template:="E:Program FilesLogicaCMGTemplatesEDMS_template.dot", NewTemplate:=False, DocumentType:=0

End Sub

In Excel I have writen:

Sub New_Document()
'New Document Macro
' Replaces File | New
Workbooks.Add Template:="E:Program FilesLogicaCMGEDMS_template.xlt", NewTemplate:=False, DocumentType:=0

End Sub

But when I run this I get an error stating that "NewTemplate" named argument not found.

Does anyone have any ideas?


Post your answer or comment

comments powered by Disqus
I have a directory that contains close to 1,000 workbooks (C:Templates) and I was wondering if it was possible to use VBA to open each of these templates one by one and either insert a hidden row from a closed workbook, or have a workbook open, and copy the column into the workbooks in the directory and then make the column hidden. Anywho, I was wanting to programatically do this as opposed to manually if possible.

Server Errors When You Use VBA to Open or Publish a Web to Different NTLM Domain

Hello again!
I do a LOT of searches on here, and a lot of reading. I could have sworn that last week I found a thread on using VBA to open several workbooks, print predesignated pages, and close the workbooks.
I have searched using a lot of combinations...I may be mistaken and read that somewhere else (Highly unlikely as I am like...always here).
Anyone remember a thread like that? I have written a code, but thus far it is purely decorative and I would like to re read the ideas.
Thanks

I have a safe data source, where large files are always stored in a .gz

Is there a way to get excel VBA to open up the .gz file?

In the old old days, there used to be a windows command line that would extract files from a windows zip file, but I can't seem to find that anywhere. There has got to be an automated way to do this....

Thanks very much in advance.

Joey

How can I use vba to open an Excel workbook that has passwords for both opening and for modifying? I think this is close:

Workbooks.Open "Full Path Name", Password: ="XYZ"

However, using that prompts me for a second password (to modify the workbook)

Hi All,

I want to use vba to open a file where the name changes slightly each week it comes in. Let's say the file name is f:/Folder A 05212009

I can do this to open it:
But, next week when the file name is Folder A 05282009, it won't open. Is there any way to write the code so that it picks up
this pattern and can open it each week?

I greatly appreciate any help you can give me!

Is it possible to be able to code within excel 2002 VBA to open up a zip file and extract and open an excel sheet thats in it?

I'm trying to have a report automatically grab a zip file off a pc that has an excel spreadsheet enclosed and extract/open it so that I can start using it.

Thanks!

DJ

Greetings,

I have a template I created which effectively imports a fixed-width file (in this instance, titled "*.upc") which contains product information. I have a folder full of these files, and I know how to automate a spreadsheet using VBA to riffle through the list of files in a specific folder (opening the ones with the extension ".upc").

HOWEVER, the template will automatically prompt for a file to load in (defaulting to the first ".upc" file I used when creating this Excel template - using the Import Wizard), which works fine when manually opening them. BUT, I need to automate this to occur again and again, processing all ".upc" files in the folder (opening them using this template and then saving them as an Excel spreadsheet). This is where the problem comes in, I cannot seems to "seed" the template when it opens with the ".upc" files one at a time.

It either opens up with no data (when attempting to automate), or it opens just the first one I tried, or it prompts for what file to open. I will attach the Excel template file below.

I need to be able to tell this Excel template which file to open up (one at a time), which will come from a list of variables (code such as this):

This part is great and searches the directory (folder) properly:

	VB:
	
 wbk = Workbooks.Open("E:Work_FoldersCISCustom_Software_DevelopmentSeneca_OpticalSpexUPC_Testempty_workbook_1.xls") 
MyDir = wbk.Path 
 
With Application.FileSearch 
    .NewSearch 
    .LookIn = MyDir 
    .SearchSubFolders = False 
    .Filename = ".UPC" 
    If .Execute > 0 Then 
        Application.ScreenUpdating = False 
        For Each vaFileName In .FoundFiles 
             'loop through each found workbook
            ProcessData vaFileName 
             'pass workbook fullname to process routine
        Next 
    Else 
        MsgBox "There were no files found." 
    End If 
    Application.ScreenUpdating = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The "ProcessData" function is the part I need the help with. This is what I tried first, then secondly (with RichieUK's help):

My attempt:

	VB:
	
 wbk2 = Workbooks.Open("E:Work_FoldersCISCustom_Software_DevelopmentSeneca_OpticalSpexUPC_TestUPC.XLT") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
RichieUK's help:

	VB:
	
 Worksheet 
 
Workbooks.OpenText _ 
Filename:=Fname, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth 
 
Set wbkData = ActiveWorkbook 
 'this is an unusual approach - normally, _
 'Set wbkData = Workbooks.Open(filename:=whatever) _
 'Is the format adopted.  Doesn 't seem to work with OpenText
 
Set wsNew = ThisWorkbook.Worksheets.Add 
wsNew.Name = wbkData.Name 
 
wbkData.ActiveSheet.UsedRange.Copy Destination:=wsNew.Range("A1") 
 
wbkData.Close savechanges:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
RichieUK was on the right track with his help, but it was not opening the template file with the proper format...

After opening the file into the template (one at a time, as there are 480 of them), I need the VBA code to then instruct the workbook (the Excel template file) to Save As an Excel spreadsheet file (using the ".upc" file name as the Excel filename - which at this point could be a variable).

1) How do I tell the template via VBA to open the specified file (for instance, the first iteration will yield the "b0028.UPC" which is to be opened into the template, then the second iteration needs to open the next ".upc" file in the folder which is "b0044.UPC", continuing until no more ".upc" files remain to be opened).

2) After opening the individual ".upc" files into the Excel template (#1 above), I then need it to Save the file as a corresponding Excel spreadsheet - thus "b0028.UPC" needs to be saved as "b0028.xls", and "b0044.UPC" needs to be saved as "b0044.xls", etc.

Thanks in advance, RichieUK has been a great help to me (previous posts yesterday and today). I am started to "get" Excel VBA (which is much different than the Access VBA which I am familiar with).

Thanks,

Colin

I would like to open a template and have the invoice number update by one. Please help with the needed VBA...

Thanks,

Tracie

Hi everyone,

I very new to VBA and am writing an Excel program to use daily. I need the spreadsheet to open a file for me daily, but the catch is the file name changes each day to reflect the current date. For whatever reason, I cannot get my date to format properly to write it on my own and need help.

File to open: 20120111, where:
2012 = year
01 = month
11 = date

So again, if I write a code to do this, I need it to open file "20120112" tomorrow.

Thanks in advance,
Alex

Hi,

I have a folder (C:userdatafiles) with a list of files such as:

userdata-01-01-2012.xls
userdata-01-02-2012.xls
userdata-01-03-2012.xls
userdata-01-04-2012.xls

How can I use vba to to open the file with the latest date? I cant use "last modified date" as the files could possibly be opened and changed at later dates. I need to be able to determine the latest date based upon the filename.

Many thanks

Hello All,
I'm opening Excel from Access using a VBA and I want Excel to open on a particular sheet.
The code is

	VB:
	
 OpenSpecific_xlFile() 
     '   Late Binding (Needs no reference set)
    Dim oXL As Object 
    Dim oExcel As Object 
    Dim sFullPath As String 
    Dim sPath As String 
     
     
     '   Create a new Excel instance
    Set oXL = CreateObject("Excel.Application") 
     
     
     '   Only XL 97 supports UserControl Property
    On Error Resume Next 
    oXL.UserControl = True 
    On Error Goto 0 
     
     
     '   Full path of excel file to open
    On Error Goto ErrHandle 
    sFullPath = CurrentProject.Path & "Cover Sheet.xlsm" 
     
     
     '   Open it
    With oXL 
        .Visible = True 
        .Workbooks.Open (sFullPath) 
    End With 
     
     
ErrExit: 
    Set oXL = Nothing 
    Exit Sub 
     
ErrHandle: 
    oXL.Visible = False 
    MsgBox Err.Description 
    Goto ErrExit 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anyone see how I can chage this specific code to open a sheet called "Guest"?

Hi, I am using a simple "Workbooks.Open Filename:=" line in VBA to open a webpage from our internal work website. It works fine for some pages but for others I get the error "Cannot open the file because the specified file name is too long. Rename the file with less than 256 characters". Basically as you can see the URL is too long but since it changes each day (the report I am running chooses the last business day) I can't use something simple like TinyURL without plugging it into the website each day and generating a new URL. I have also tried a Function I found online (see below) but this doesn't work because it creates the same URL each time. Is there a way around this at all? I can't get work to simply reduce the size of the web address, believe me I would love that. Any help would be much appreciated!


	VB:
	
 
Sub testme() 
     
    MsgBox GetTinyUrl("http://www.longwebaddressgoeshere.../") 
     
End Sub 
 
[COLOR=#2060a0]Function[/COLOR] GetISGDUrl(url [COLOR=#2060a0]As[/COLOR] [COLOR=#2060a0]String[/COLOR])
[COLOR=#2060a0]As[/COLOR] [COLOR=#2060a0]String 
Dim[/COLOR] xml [COLOR=#2060a0]As[/COLOR] [COLOR=#2060a0]Object 
Set[/COLOR] xml = CreateObject([COLOR=#c03030]"MSXML2.XMLHTTP.6.0"[/COLOR]) 
 
xml.[COLOR=#2060a0]Open[/COLOR] [COLOR=#c03030]"POST"[/COLOR], [COLOR=#c03030]"http://is.gd/api.php?longurl="[/COLOR] & url,
[COLOR=#2060a0]False 
[/COLOR]xml.Send 
 
GetISGDUrl = xml.responsetext 
 
[COLOR=#2060a0]End[/COLOR] [COLOR=#2060a0]Function[/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Thanks,
Simon

I'm trying to open a file using VBA, part of the filename changes each week. For example, Atlanta_9EM_weekly.rep will change to Atlanta_8RH_weekly.rep. the code I'm using is:
fname=Atlanta_" & "???" & "_weekly.rep. It appears that the program is looking for file named: Atlanta_???_weekly.

What's the VBA code to open or call Windows Explorer to a specific directory?

Can anyone help me with the VBA syntax to open a closed workbook that is in a directory that is two folders up?

right now what i am doing is the following:

filePath = "I:PeTeXExcel" & fileName
Workbooks.Open fileName:=filePath

However, the I: is the network drive which I map to, and it may be different for others, so i would like to change my VBA code so that it is not referring to a specific drive, but just two folders up...

thanks in adavance.

Hi there folks,

I need to build two VBA routines that when activated will...

1) open the OPEN dialog box and allow the user to open an Excel file of their choice.

...and...

2) open the Save As dialog box and allow the user to save an Excel file to a location of their choice.

...be ****ed if I can't remember the code to bring up these dialog boxes!

Many thanks,

Andy

Can any one help with the VBA code to open and copy this web page to a blank Worksheet?

I have tried to develop a macro using Data > Get External Data > New Web Query etc to no avail, as soon as I enter the URL it states that it can not load the URL.

I have also tried opening a new workbook directly from the web page concerned but again no luck. I am using Excel2003
TIA GT

A member here posted this VBA to help me split up all sheets in the active workbook into their own documents.

Sub splitsheets_into_own_docs()

Code:
Dim myDir As String, ws As Worksheet
myDir = "c:split"  

For Each ws In ThisWorkbook.Sheets
    ws.Copy
    ActiveWorkbook.SaveAs myDir & "Store_" & ws.Name & "_restoffilenameblabla"
    ActiveWorkbook.Close False
Next

End Sub
This works great, it splits up all the sheets in the workbook into their own documents, for as many sheets as there are in the master workbook.

What if I have an excel document in c:tempt.xls, and that is a single sheet document, basically a template document.

Is there any way to adjust the script so that it copies that t.xls as the first sheet and then all the rest from the split would be sheet (2) for each individual document ?

Or maybe even a new VBA that I could execute as a second run, and it would grab all *.xls documents that are in c:split and add that t.xls as the first sheet and whatever is inside the doc, move that to the second sheet ?

How hard would something like that be possible ?

Thank you for your time.

'Sup?
How can I open the UserForm code module by macro. Like when I use VBA to open some regular macro, you know with 'Application.Goto Reference:="the name of the Sub"'. This doesn't work if the Sub is in the UserForm. How should I name the Reference? Any ideas?

My workgroup templates are Excel97 and Excel 2003 won't open them. No error
it just doesn't open them.

Even if I save a new template in 2003 and save it to the workgroup location
it fails to open. They can be seen in the open dialog box.

The workgroup location is on a Netware, but that shouldn't matter as they
are in P:MSOFFICEXLSTARTTEMPLATE

If I move them to the same folder as the Word Templates then they open, but
the File extension is displayed.

This can't be by design.

Hi,

I have a problem with trying to get a template to open from inside a sheet.

At the moment I have a large workbook which has lots of data that the template uses for lists boxes and INDEX/MATCH lookups. The problem I've got is I want to allow the user to click a button which will create a template for them from the original sheet (so they only have one sheet to use).

Trouble is when I open the template through windows explorer whilst the workbook with all the information is open and I get no errors and all the functionality works fine. However when I try to use VBA to open a template I get the "Update Links" message which no matter what option I press breaks the functionality of the sheet.

I've tried;

Workbooks.open "sheet1.xlt"

and

Workbooks.Add Template:="sheet1.xlt"

The first one just opens the template for editing and the second gives me the update error I'm trying to avoid, and for some reason I can't seem to add the UpdateLinks:=0 thing either excel just says it's an invalid arguement.

Can anyone help?

I would like to open my workbook via a VBA so that I can try writing some code to protect the workbook from being given away. Since I am very new to VBA, I need to be pointed in the right direction. In the VBA, I will insert some lines of code that looks to see if a specific date has passed. I will, by this method have a spreadsheet that has an expiration date to it. (The sheet will work for only so long) If the date is still in the future, the VBA opens up the workbook. If the date has passed, the VBA doesn't open it.

Any help or steering me in the right direction is appreciated.

Thanks,
Excel Kurt

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:DocumentsSHIFTLOGShiftlog with Prod.mdb

Thanks,
EMoe


No luck finding an answer? You could always try Google.