I want to schedule a report to run overnight when I'm not at work, and it needs to save as a filename with the previous day included in it
For example, the filename needs to be saved as
C:Test_variable_ver1.xls where variable is equal to day(today()-1) in excel terms.
I have a folder which contains a number files. What I would like to do is for my spreadsheet to look into that folder and write the filenames to the sheet. And everytime i run it, the file names are just added on.
View 4 Replies View RelatedI have about 50 CSV files in a directory and would like to list them in a column that I can use them to populate a box so that a user can select the file they want in the box and open it in a new spreadsheet.
I have tried several ways to do this but nothing I do works.
At present I am using an imput box for the user to type in the filename, but if they type incorrectly, or type in a file name that doesnt exist the program just exits because of my error trap.
I need tht How to rename the Filenames in an Folder using macro.. Is it possible, How we can do it..
Example: In an Folder 200files are there with extension .jpg/xls/bmp/txt/doc files, I want to rename tht those files names,
Example1:
Source File: TestImage.jpg
I want rename as
Destination file : image.jpg.
Not only Single file rename..if I want to rename Bulk means?...
I am attempting to split my workbook and save the sheets as individual files. Simple enough? yes. The issue I am running into is that when the sheets export I want their filename to be pulled from the value of a cell on a master control sheet. While I am pretty sure these can be done separately, not sure how to really do them as a whole within a one click button. Also an issue I am running into is that when I click my export button on my control page, the sheets that are saved have lost some of the page formatting.
SHEET1 is my control sheet
Here is the code that I was using to export the sheets.
[Code] ......
I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.
View 7 Replies View RelatedI'm trying to retrive files from a given directory into excel.
I already have this file from another posted message:
Sub GetFileNames()
Dim F As Long
Dim FileName As String
Dim TheNames As Variant
ReDim TheNames(1 To 1)
FileName = Dir$("*.*")
Do While Len(FileName)
F = F + 1
ReDim Preserve TheNames(1 To F)
TheNames(F) = FileName
FileName = Dir$()
Loop
Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames)
End Sub
But I want to be able to set the path myself
Have an array that contains file names similar to these:
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt
I need to sort the array in ascending order based on the file name. The file name is after the last “” and starts with a number(s). From my example above I would like the resulting sort order to look like this:
\serversharesmysharesomefolder1_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder3_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder25_s6_macro_cycle22_2Ft10p75.txt
\serversharesmysharesomefolder32_s6_macro_cycle22_2Ft10p75.txt
I want to use the Application.GetOpenFilename to populate the array and then process the filenames.
I assumed you data starts in "A1"
Results start "F1"
Code:
Sub MG19Dec38()
Dim varFiles As Variant ' used to hold array of file to open
Dim ray
Dim j As Long, i As Long
Dim Temp1 As Integer
Dim Temp2 As Integer
Dim Tem As String
' ray = Application.GetOpenFilename("Txt Files (*.txt), *.txt", , , , True)
I have a userform which prompts a user to select two files from the directory. Once these two files are open I will extract data from one into the other, the only problem is these filenames will change and I need to be able to determine what their names are.
I have this code below:
Private Sub CommandButton1_Click()
FiletoOpen = Application.GetOpenFilename _
(Title:="Please choose a file", _
FileFilter:="Excel Files *.xls (*.xls),")
If FiletoOpen = False Then
MsgBox "No file specified."
Exit Sub
Else
Filename1 = FiletoOpen
TextBox1.Value = Filename1
End If
End Sub
If I could somehow extract the exact filename, not full path, from the variables Filename1 and Filename2 I would be fine I guess, I just don't know how to do that.
I took this code from another post and modified it slightly to look in my directory "C:cherylpostal" and list the names of all the files contained. I then have them displayed in a combo box. They are all CSV files, but I would really like them to appear without the .CSV extension. For example, my COQ4 file appears as COQ4.CSV but I would like it to just display COQ4. I can't seem to make it work however.
Sub Macro1()
Dim rngOut As Range
Dim strPath As String
Dim strFile As String
Set rngOut = Range("F1")
strPath = "C:cherylpostal"
strFile = Dir(strPath & "*.*")
If strFile = "" Then
MsgBox "No files matching criteria can be found in " & strPath, vbExclamation
Exit Sub
End If
Do While strFile <> ""
rngOut = strFile
Set rngOut = rngOut.Offset(1, 0)
strFile = Dir
Loop
End Sub
I have done a search in the forum, and there are many results on browsing directory and opening files, but they are not what I have in mind.
What I have is different folders for different months (e.g. January, February, March and so on), and in each folder I have the same file names (e.g., in January folder, I have files called A, B, C. In February, I also have files A, B, C but these are different from the files in other folders)
Basically I need a macro which can open a specific list of files.
So first step, the user should be able to browse the directory, then select a folder (no need to open, not sure if it makes any difference though). I need the user to be able to select different folders because the macro should work for the different months in different years.
For this, I have found this
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "c:\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
Else
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function...........
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.
I realise this is not strictly an excel question but it forms part of my VBA code within excel
I have been writing a VBA program (with some fantastic help from you guys) part of which writes to a batch file to rename files stored in a given folder. Unfortunately this fails if the original files have spaces in the filenames. The batch file contains command lines such as: ...
I have been using this code to choose multiple files to manipulate:
View 3 Replies View RelatedI'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
View 2 Replies View Relatedgetting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.
View 1 Replies View RelatedThe reports I build work from a basic template with 10 or so separate, slightly different versions. In order to make them I currently update the data in the template and then click on "File>Save As..." ten times, renaming the file as appropriate.
Example:
Overall Report for yyyy/mm/dd
Partner A report for yyyy/mm/dd
Partner B report for yyyy/mm/dd
ad nauseum..
Is there an easy macro that could save and rename my file multiple times? It would need to accept manual input for the date. Preferably it would be something that I could easily tweak to change the filenames and number of reports saved as this is most of what I do.
I am trying to develop an excel macro to accomplish the following:
-I have a master workbook for collecting data
-One folder will be used to dump excel files into; I don't know the filenames (other than "*.xls"), but I do know from which cells I will need to retreive data (they will be the same for all files)
1. I need to retrieve data from these excel files
2. After getting data from a file, it is then moved to another folder
3. Next the retreive data, then move is performed on the next excel file until there are none left in C:IN
Upon searching this forum, I found a way to open each of these files one by one (without having to know the filenames): Using a script to open unspecified file name
- For me, the obstacle seems to be not knowing how to do what I need to without knowing the filenames
- Again, the issue - retrieve data from these files, then rename/move these files
I was wondering if there is a way to list all filenames from a directory on a worksheet including the files located within it's subfolders? I found a bit of code at [URL] that is as follows, but I can't seem to get it to read the files in the subfolders... Also, would it at all be possible to hotlink each entry so that the user can double-click the name in the sheet and open the file?
VB:
Sub ListAllFile() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim ws As Worksheet Set objFSO = CreateObject("Scripting.FileSystemObject") Set ws = Worksheets.Add 'Get the folder object associated with the directory Set objFolder = objFSO.GetFolder("C:") ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:" 'Loop through the Files collection For Each objFile In objFolder.Files ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name Next 'Clean up! Set objFolder = Nothing Set objFile = Nothing Set objFSO = Nothing End Sub
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer
[Code] .....
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies View RelatedCan a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".
At moment I use following
vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0
vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0
until...............................
I'm having a hard time understanding how to accomplish what seems to be a simple result.
I need to display one of two words, based on whether or not a pair of values are above or below the criteria.
FIRST:
IF H6 is greater than 5000
AND
IF AB6 is greater than 25000
Display: Double
SECOND:
IF H6 is less than 5000
AND
IF AB6 is greater than 25000
Display: Single
There is no 3rd scenario, even though logically there should be.
I am trying to put variables in this URL which is related to yahoo finance :
.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"
I defined at the beginning
Dim start_date As Date
Dim end_date As Date
Dim datestring As Variant
start_date = #1/31/2001#
end_date = #11/26/2006#
and put them in datestring
I passed the datestring to a new sub which has the URL:
.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"
So, my question is, i tried to put the (1/31/2001) and (26/11/2007) which is in the above URL which is separated in variables and the URL remain the same
I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.
What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4
I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")
vlookup with 3 different variables, for example cells k4 k5 and k6 can be changed to give different variables. Is it possible to have a vlookup function in cell k9 which returns the correct % when the 3 variables are chosen. example, blue boat 48 would return %value of 21%
View 2 Replies View RelatedI am trying to count the status and type of some work so:
Column A would contain the status of the work e.g. open, in progress, closed etc.
Column B would contain the department: ict, development, operations, etc.
I want to do a summary that shows: How many are in ICT are open, closed etc.
I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.