How To Create And Add Password To Default Windows Zip File Using VBA Macro
Mar 17, 2013how to create and add password to a default windows zip file using vba excel macro.
View 2 Replies
ADVERTISEMENT
Open Windows File Chooser At Default Location
Feb 14, 2012Is there any way of making the default directory location for application.getopenfilename work properly with shared locations?
Presently I use ChDrive then Chdir but I cant use ChDrive if workbook is opened from a link in email for example. Please see below
I have a VBA routine that asks the user to load an existing file on startup.
I want the window for the file selection to open at the same folder every time. (the folder is on a shared drive)
The workbook to run the code will always be located on this same share at a fixed location.
My present code builds the path to the desired opening folder by collecting the drive letter from the current working directory (which has to be done as everybody maps there shares to different letters here, its not guaranteed to be the same! )
then i ChDrive to this letter.
Then I ChDir to letter plus myKnowndirPath
This works fine, the window always opens at my required folder. But only as long as the user has navigated to the folder containing the spreadsheet and then ran it from there. Because that way a drive letter is available.
However i found that if i sent a link to the workbook or its home folder. And the user opens the link or a windows explorer from that link and then runs file from there.
Then it stops working beacause there is no local drive letter in the working directory path. i.e. working directory shows as
'network \shareddrive
ootafolderanother arget
instead of something like
J:afolderanother arget
Create New File In Windows
Jun 10, 2009I am looking for the best way to create a new file in windows. I am just looking to create a save filename template. After I run a macro, a graph is created and printed to pdf. I want this macro to create a filename, so that when the save as dialog box opens, I just have to doubleclick the existing filename. Then I save the excel workbook as another.
For example, after I run the macro, the pdf printer will open up a save as dialog to c: emp. I want to have this macro create two files there, one DaveGraph.PDF and one DaveExcel.xls. They really won't be files, just names. They can be 0kb files. I want it to be as efficient as possible. I know I could create a new workbook and save it there with the correct name, but isn't there an easier way?
Default View Of VB Windows
Feb 18, 2010Having trouble setting VB windows views back to deafult ...
View 14 Replies View RelatedReset Default Colours In Windows 7?
Apr 24, 2012We have only just converted to Windows 7 from Windows 2000 and can't find the option to reset default colours. In Windows 2000 it is in: tools/options/colour.
View 2 Replies View RelatedLaunching Excel In Separate Windows As Default?
Aug 16, 2012Each time I click on an Excel file the worksheet will open up within an existing Excel worksheet that I have open. This is frustrating when you are working with two monitors or screens (laptop screen and additional monitor) and want to view each worksheet separately in separate screens. What I end up doing, which is not efficient, is I'll open the second worksheet which will open up in an existing worksheet that I have open and then I'll close it, and then launch a separate Excel worksheet and pull open the recent file and I'll have two open worksheets (not within same Excel worksheet I already have open). This allows me to grab and pull one worksheet in a separate screen and leave the other as I need both open on two monitors.
Question: is there a way to default Excel when opening or double clicking an Excel file to automatically open in a separate instance of Excel and not in an existing open worksheet?
File Browsing In Macro - How To Change Default Start Location?
Sep 28, 2008I use an image analysis program ImagePro which can call Excel within it's macro language (which seems to be visual basic). I looked up on this site how the change the drive (ChDrive command). But still when the Excel section (after With oExcel) executes the default file location in the browser is in My Documents on the C: drive.
Prior to this code Excel has been launched by this ImagePro macro, and a file Cumberland Template has been opened from the C: drive. Now I would like to do a Save As, but have Excel start at the I: drive location.
Create Username And Password To Login Into A Macro Enabled Workbook
Jan 3, 2011I need a macro ie when a user opens a macro enabled workbook,he should be asked for a username and password to access workbook. Administrator should have right to create users with reset password rights
View 5 Replies View RelatedMacro To See If File Exists Or Password Protected
Nov 29, 2009I have written a macro to select a range of cells one by one and open the excel files mentioned in them and execute a task. It works fine. But I want the system to see if the file exists in its folder or if the file is password protected (for opening). Either of the case the file should be skipped without a warning message and any attempt to open. I need a macro for that. Following is the way the cell contents are
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-LEGGINGS-13246.xls
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-PANTS-13247.xls
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-SHORTS-13244.xls
I:ORDERSS-S 10MICHELLEGAMESWORLDWIDE COMPANYWORLDWIDE COMPANY - GAMES - 10014.xls
And following is the VBA code I wrote. It it almost stops when reaching a non-existing file name
Password Protection :: Asking For Password Of An Old Deleted File
May 27, 2009When opening a new Excel file, it is asking me for a password to an old excel file that was password protected, which I have deleted from my computer. It is still asking for this password every time I open a new or existing file. When I hit cancel it lets me open the file.
View 11 Replies View RelatedCreate A Windows Folder On A Server
Feb 3, 2009I would like a windows folder to be created on a server from cell Cbut to be filtered by the Data in B.
dwights folder on teh server to create '124 elm street' from the in C1
james folder to the server to create the folder '15 killfops' street'from C5
\server empdwight
\server empjames .....
Create Shortcut On Windows Start Menu
Jan 29, 2009Split off from Create Desktop Shortcut With VBA Macro
Try WSH.SpecialFolders("StartMenu")
List of Special Folders at link -
http://msdn.microsoft.com/en-us/libr...xe(VS.85).aspx
Create Instance Of Microsoft Windows Journal
Feb 14, 20081) When using the command below, how can I determine what the "XYZ" component could be. I see plenty of examples of it being used to start a MS Word session or Powerpoint but I'd really like to see a definitive list.
At the moment, I'm not even sure what to type into Google that describes this part of the command's argument.
Set target = CreateObject("XYZ.Application")
2) I'm just a frightened noob on this site and I tried to follow the advice on bumping my previous post on this subject, having altered my plea. All I got was a telling-off for being a double-poster and my old old thread sat where it was despite my 'Bump' command. Do you have to have at least one reply before your thread moves?
Get File Path From Windows Registry
Sep 4, 2006How to search and get the path of a folder present in windows registry using VBA excel.
View 9 Replies View RelatedChanging File Names In A Windows Directory
Nov 23, 2009Using VBA, can anyone please advise if it is possible to change the name of a large number of files in a directory. I want to get each file name as a string, truncate the first few characters, then change the name to the new filename, all without opening any of the files whose names are to be changed.
View 9 Replies View RelatedHow To Create Macro To Change Value In CSV File
Aug 21, 2012I have a CSV file contain many fields and all fields have value (positive & negative values). I have 3 questions here:
1) This file is created on Friday. So the date in Field1 is Friday's date. I want to change the date to Saturday date. How can i change it using macro?
2) I want to set the value for fields 24 until 26 to 0. I want the script to force the field value to be 0. How can i do that by using macro?..and fyi, my CSV file have header in the first row. Below i illustrate my original csv file and the expected output.
3) How can i automate the macro to run every Friday afternoon?
Original csv
Field1, Field2,....,Field24, Field25, Field26, Field27
"20120803","ABC",...,9.032,24.52,-6.325,21.12
"20120803","ABC",...,5.242,-1.53,7.005,21.12
Expected output csv
Field1, Field2,....,Field24, Field25, Field26, Field27
"20120804","ABC",...,0,0,0,21.12
"20120804","ABC",...,0,0,0,21.12
Create A Separate File After Macro Done
Apr 19, 2006How can I code to create a copy of a worksheet in an excel file I am using a macro on, after the macro is done processing? In other words, when the macro completes processing, create a copy of the worksheet labelled "Output", and save it in a directory that the user chooses? Also, would it be possible to create a .pdf file?
View 9 Replies View RelatedCreate New File & Name Via Macro Code
Sep 1, 2006I have been trying to create a macro that creates a new workbook and prompts the user to enter a new filename for it. I'm getting stuck with the code.
View 3 Replies View RelatedEnsure Active Workbook When File Opened By Windows Scheduler
Jul 23, 2009This code opens a UserForm in the Workbook_Open event when the file is opened. After 10 seconds, the message is supposed to go away if the user doesn't click anything, then macros that follow are supposed to run. It is intended to allow a user to stop the macros. This works when you open the file manually (macro security must be low), you'll see a msgbox pop up after the UserForm goes away.
However, when the workbook is opened as a Windows Scheduled Task (Windows/ Control Panel/ Scheduled Tasks), the UserForm does not come to the front of the screen unless a user actually clicks on the window. It just flashes at the bottom indefinitely.
Custom Macro To Create New File From Data
Jun 12, 2009I'm looking to create a new file from data in my table. I don't want to even imagine having to do this manually again...I'm optimistic there is a solution. All the data needed to create the file is in the table, but i need it stacked and organized in a weird way. It's almost to hard to explain...so I color coded an attachement that basically says it all. It's pretty much the same thing repeated over and over except the last 2 lines. It's just a really messed up organization. In the real version I need the new file in a new workbook. I'm extremely grateful to anyone who can automate this thing
View 2 Replies View RelatedCreate A Border Macro When Open The File
Jul 26, 2009I have recorded a macro which will create the border for me when the file is opened, but it turned out too long. if someone can show me how to reduce the codeing. I have the attached file.
Range A5 to E20 thin Border all sides and thick border allround (16 rows)
Range A21 to E36 thin border all sides and thick border allround (16 rows)
end range is A356.
Fixing Macro: Only Create Around 53 Text File
Aug 23, 2009First need to create a "Text" Folder in your desktop, then try running the macro. It will create a number of text files inside the "Text" folder.. And they contain the used cells from each row. It's supposed to create 982 text files, with the text name referred to the first column. Problem is, if you try running it, it will only create around 53 text file
View 2 Replies View RelatedMacro To Create Automatic Backup Of File
Oct 27, 2013Is it possible to create a macro that automatically saves a backup of the excel document in another desired location?
I have this formula:
Sub backupbutton()
Dim fname
fname = "D:" & Format(Now, "dd mmm yy hh mm") & ".xlsm"
ThisWorkbook.SaveAs Filename:=fname
End Sub
But that just keeps on making multiple copies of the file rather than overwriting the backup in the D: location.
Also, that particular macro requires me to have to click on a button in order for it to work but I would prefer that it happens automatically when the original file is saved.
Macro To Create New Folder And Save File
May 27, 2009I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks
Sub Save_wrkbk()
Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next
strDirname = "Fungicide Quotes"
strFilename = Range("d4:f4").Value
strDefpath = "C:My Documents"
If IsEmpty(Filename) Then Exit Sub
MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "" & strFilename.......................
DAO Query On Password Protected File: Could Not Decrypt File
Dec 9, 2006I have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file.
' Const stExtens As String = "Excel 8.0;HDR=Yes;"
Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;"
Private Sub Execute_Query(SQL)
'Instantiate the DAO objects.
strDb = ThisWorkbook.FullName
Set DAO_ws = DBEngine.Workspaces(0)
' code fails on the line below
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly)
End Sub
Create Default For Dependent Drop Down List
Feb 18, 2010I'm using dependent drop-down lists. In the first box the user can choose a color which drives a set of options in the second box. (e.g. User selects "Red" and in the second dropdown they can then choose "Red A", "Red B", "Red C", etc.). Right now, if you select "Orange", it will still show "Red A" from the previous selection until the user selects the dropdown in the dependent box to refresh the list of options. Is there a way to make it so that second dropdown box will default to "Orange A"?
I tried writing some code to do this, but I can't seem to make it work. I've attached my file.
Need To Embed A Sound File & Create A Macro To Play It..
Jan 20, 2010embed a audio file (.wav or .mp3) into a Excel Sheet and be able to have it play from a macro? I need the Clip to stay in the file not refer to a location on the pc and have it play from the sheet not open media player. Can this be done?
View 7 Replies View RelatedCreate & Save Workbook To Same Path As Macro File
Aug 21, 2008I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."
Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete
End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................
Macro To Create New Line In Inventory Pointing To Specific File
Jul 9, 2014I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.
The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).
Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.
What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.
I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like
"='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.
Create Master File With Macro Built In That Will Allow Multiple Users To Use It At Once
Feb 7, 2014I am trying to create a master file, with a macro built in, that will allow multiple users to use it at once. The macro is to open a dilouge(sp sorry) box showing the contents of a specific folder, allow the user to selct one of the sheets, then copy and paste the set details from the hidden tab on this sheet (All sheets will be the same barring title), append the details to the master list in the first empty row.
I've got this far thus
Sub Macro1()
'
' Macro1 Macro
'
'
' Modify this folder path to point to the files you want to use.
FolderPath = "My Folder name here"
' Set the current directory to the the folder path.
ChDrive FolderPath
[Code] .....
So I can copy the row, but I can't get the first part to open .