I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached)
Obviously if the folder already exists I want the code to stop.
But this code will have to generate a different folder for each row within the spreadsheet.
I want cell D28 to be text of the location to the file selected through CommandButton1.. tia
This is what I have so far:
Code:
Private Sub CommandButton1_Click()
Dim browse As Long
Set Application.GetOpenFilename("All PDF files (*.pdf*), *.pdf*", , _
"Choose a Filename") = browse
ActiveSheet.Range("D28") = browse
End Sub
How to query the current folder location in VBA?
The macro will need to import text from a file (file.out) located in iteration folders (iteration_001, etc) but I need to start by seaching the location that the excel file has been saved in.
which part of this macro I need to edit to make it open a folder I want?
Sub OpenFolderRequest()
YesNo = MsgBox("Would you like to open the folder to see" _
& vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
myval = Shell("c:winntexplorer.exe c:winnt", 1)
Case vbNo
End Select
End Sub
I have tried changing the (c:winntexplorer.exe c:winnt) to the location name of the folder I want to open (S:Stock ControlSTOCK CONTROLOrder Confirmation)but it doesn’t seem to work.
I'm trying to write a set of macros and one of the macros needs to move a folder with subfolders from P:CJ to P:WO
Now I've been using the CopyFile State. Is there something similar because in my search they talk about using FileSystemObjects and I have now clue how to use those.
I am running WinNT and need to find the folder containing the default icons available for use on buttons in the toolbar window. I can copy the image but it saves as a picture (device independent bitmap). I want to be able to send the icon to other people so they can put it in their directory and choose to select it.
View 3 Replies View RelatedI have a folder "Macro" in the below location in my PC:
C:UserskkumarDesktopMy WorksMacro
There are 4 excel files in this folder:
Sales.xls, Quantity.xls, Forecast.xls and Macro.xls.
Macro.xls has 3 sheets: Sales, Quantity & Forecast.
I want a macro which will pull all data in:
Sheet1 of Sales.xls to "Sales" sheet of Macro.xls
Sheet1 of Quantity.xls to "Quantity" sheet of Macro.xls
Sheet1 of Forecast.xls to "Forecast" sheet of Macro.xls
Also one additional requirement is if I copy the Macro folder to some other location in my PC the macro should still work.
When someone right clicks on a cell and chooses "Hyperlink" current folder is being displayed as a default.
is there a way to change this for a particular workbook through VBA or API calls ?
I would like to always have "c:" as the default folder no matter where the workbook is stored.
I have a template for information that needs uploaded into a database via CSV. This database is then access via a HTML front end. As part of this there is a 'folder structure' with in the front end. This is displayed in a database table (See attached excel file for example data structure)
Currently I have this feeding into a drop down list which is somewhat cumbersome so want to make it more efficient and easier to use. I had toyed with the idea of adding more columns into the excel template and split the string up to populate however this isnt exactly professional looking.
My current thinking is, is there a way of putting this data into a listbox which is easy to navigate? Ideally in a folder tree navigation structure, I know this is possible for a windows folder structure
Folder Structure.xlsx
i want to use the "Browse for Folder" to select the folder where the files is in eg. E:My DocsGlobal and list the folder location and filenames to table (column I:J), and color them according to every folder location.
I would like the "Browse for Folder" windows to be able to select multiple files instead of just one file a time.
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
View 2 Replies View RelatedI have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet
[Code] ........
1. A workbook is closed
2. In addition to the original workbook being saved, a copy of the workbook with the current date is also saved to the specified location of my choice.
For example
"C:UsersUSER1DesktopBackup Test as of 02-10-14"
3. If a copy of the workbook is already saved with today's date, then overwrite it automatically without prompting the user.
4. All the above happens without any user interaction.
If a user inputs "Dispatch" into any cell on column G, I need to generate a Msgbox pop up.
View 3 Replies View RelatedI've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View 2 Replies View RelatedI have an Excel file to distribute to users. This file must contain Hyperlinks to a Word file which i will enbed in a sheet of the Excel file itself. My question is this: Is it possible to produce Hyperlinks that go to the specific part of the Word document? So far, i am only able to Hyperlink to the first page of the Word doc, by doing a macro.
View 2 Replies View RelatedAny way/method to make a certain number of cells randomly generate to a specific value you want? I only want to use the number from 1 to 60.
example:
1 37
2 29
3 34
4 2
5 58
6 38
7 =198
I have a code that generate some combinations for a specific sum from a given data set. This code has two errors.
1. code does not look into data. it generates
2. Code generate those combinations for which sum is greater than target value.
E.g. first combination code generates is 3+7+10+12+12
This wrong by two means sum is
1) 44 while we need 40
2) 12 is not in data.
I am running a macro in Excel which automatically generates a PDF of my worksheet. Currently it saves in the default location but i want to modify it to a specific location - P:Emergency Services|Procative ContactForms PDF.
The current code is;
VB:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E7").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Range("A1:E43").Select
I know Excel is not the best tool for formatting sentences, but is there a way to insert a Line-Break based on a condition? (Either using Formula or VBA).
The situation is:A fixed-width cell, is a concatenation of 5 strings. Each of these 5 strings is a small sentence of 3-6 words; individually much smaller than the cell-width.2 of these 5 are fixed (one at start, one at the end). The other 3 are results of their respective "IF" formulas.Hence, the final sentence wraps over 2-3 lines.I need the 3rd & 5th sentence (the whole sentence, all 3-6 words of it) to start in a new line, only if they are going to be wrapped into two lines.
So If the wrapping results in:
String1 String2 Stri
ng3 String4 Stri
ng5
Then change it to:
String1 String2
String3 String4
String5
I would appreciate some ones help to correct a macro I'm trying to write. The macro copies 3 columns from Sheet1 to Sheet2 in a selected location. The 3rd column copied needs to be pasted in a different column in Sheet2.
View 6 Replies View RelatedI have code that has the user identify a file and then it puts that file location in a cell within the workbook
"Y:MastersProPricerENG WBS BOEXYZ.xlsx"
I want to copy a tab from the active workbook ("Resources (Spread or Load)") and move it to the workbook listed above.
I would like to use the attached "menu maker" file I found on Ron De Bruin's site using a Sheet to build the menu, yet be able to place it before the Help menu as suggested by this site [url] by finding the position of Help.
I have amended the code yet receive a "Compile error: Variable not defined" error.
I have a spreadsheet that keeps track of my travel. Column A has the date I arrived somewhere, and Column B has the date I departed, and Column C has the name of the city I went to.
I am wondering if there is a way to generate a calendar using my list that will mark those dates. For example, a calendar for the month of June 2008 that would show I was traveling from June 3 to June 14, either by marking those dates with a different color or labeling them with the city names, or even just putting an x in the box.
I have a spreadsheet where I track resources needed on a project. Each resource that is selected in the resource column has a corresponding % avg and an average number of months where the resource is used. % Avg and # of Months are found in a different worksheet.
What I am looking to do is, for example: if resource A will be on the project starting in May 2015 and assigned at 30% for 7 months, I want to populate 30% starting in the colunm labeled May 2015 and copy the 30% so that a total of 7 months are populated. Also, if any of the values change (i.e: the Resource Name or the Start Month) I want to erase all the percentages that were entered in the row. How can I achieve that with a VBA code? I have formulas that retrieve the # of Months and the % Avg already.
These are the columns that I have in my spreadsheet:
Resource NameStart MonthJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code:
Sub atest()
Dim strFileName As String
Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
I have a User form with a combo box that is populated with numbers (1 thru 50) and four text boxes for first name, last name, email & cell number.
It all works fine. However: I would like it to transfer the data to (Sheet3) in numerical order... In other words, If the user picks number 5 his data would be entered in the fifth row.(or sixth counting header). Or if he chooses number 37, his info would be entered into row 37 (38 with header) of (Sheet3)
It currently populates the next empty row.
My code is below, How would I modify it to accomplish this?
Code:
Private Sub EnterButton_Click()
'Populates GetNumber Combo Box
Dim w As Worksheet, x As Long
Set w = Sheets("Sheet2")
x = w.Columns(19).Find(Me.GetNum.Value, lookat:=xlWhole, LookIn:=xlValues).Row
w.Range("S" & x).Delete
[Code] ............
I have a excel sheet which is completely formula driven and no macros in that.
I want to macro which can save that excel sheet to a specific location.
I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
View 2 Replies View RelatedWhat I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg).
Sub ChkDates()
Dim c As Range
Dim DelRng As Range
Dim ArcRng As Range
Dim i As Long
Dim l As Integer
Application. ScreenUpdating = False
Worksheets("Report").Activate
For i = 60 To 8 Step -1
Set c = Cells(i, 33)
If IsDate(c) Then ..............