Excel 2010 :: VBA Code To Open Save As Box In Specified Folder

Apr 24, 2014

I have windows 7 and excel 2010 and am using a macro that opens up a csv file(I think) of daily reports into Excel and then automatically delineates it and formats it how I want it. I will be using this to save a new file every day for the reports from the previous day and want to include at the end of that macro a way to prompt the user to "save as" so that each day they can run the macro and enter in the date and save that report for further use. I am wondering what VBA code I could use at the end of the macro code to prompt the Save As box and if I could already have the save us set up in the following folder... "W:Daily to Fortis Excel2014(the user will put in the date here)".

I've been looking around sites and trying to figure it out. I need the file format to be the same as when you save as "Excel Workbook". I was trying to use the Saveas (filename) function and could get it to save every time as a specific file name in that location but when I run it the next day it has the same name and saves over itself. So I need the user to be able to put in todays date as the filename to create a new one every day.

View 7 Replies


ADVERTISEMENT

Excel 2010 :: Save Open Worksheet To File And Include Date Of Save?

Mar 11, 2014

am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....

I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....

I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...

View 6 Replies View Related

VBA Open All Text Files In Excel In Folder And Save Them As Excel File

Dec 7, 2013

I have some daily text files in a folder (so about 30 of them each month), which in the end of month, I need to open them up in excel, format them so that I can use the information for my analysis.

I would like to create a macro, to quickly open them all up at once and save them each individually in .xls or .xlsm format.

I am new to VBA and after some research online, I was able to have the files open with the following code. but now I don't know how to proceed further to save them one by one with the same name but in .xls or .xlsm format.

Sub Opentxtfiles()
Dim MyFolder As String
Dim myfile As String

[Code].....

View 2 Replies View Related

Open Excel Files In A Folder - Change Layout And Save

Nov 11, 2013

There is a folder with all excel files with the same structure. I need a macro, who opens one file by one in a folder, change the layout, and save it too same place with same name. Changing the layout will I do with macro record.

Sub AllFiles()
Dim MyFolder As String 'Path containing the files for looping
Dim MyFile As String 'Filename obtained by Dir function
Dim MyBook As Workbook
MyFolder = "D:LABODIESTSOST_DIEST" 'Assign directory to MyFolder variable

[Code] ......

View 9 Replies View Related

Excel 2010 :: Save Open File Location?

Nov 26, 2012

I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.

In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.

View 1 Replies View Related

Excel 2010 :: Can't Save Custom Table Style Format So That It Is Available Next Time When Open Workbook

Jun 10, 2013

I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?

View 3 Replies View Related

Excel 2010 :: Using Current Month / Date And Time In A Folder Path In VBA Code

Feb 4, 2014

I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.

1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …

2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?

View 4 Replies View Related

Excel 2010 :: VBA Code To Save File As Semi Colon Delimited Format

Jun 3, 2014

I am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.

View 1 Replies View Related

Lookup Folder From Cell Text Then Save Excel File In This Folder

May 14, 2014

I have alot of project folders on my harddrive.

All in format: I:/12345-costumer-projectname/

The five digits are unique for each project.

I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)

Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.

I have found macro to find files in folders, but none which do the above.

View 4 Replies View Related

Adding Folder To 'Open' And 'Save As' Windows

Aug 28, 2009

I had this set up in Excel 2003, but our main shared drive just went down and after remapping this link didn't work. How do I do this in Excel 2007?

I want a folder to show up on the left hand side of the "Open' and 'Save As' windows under My computer, desktop, My Documents, etc.

View 2 Replies View Related

Open/Update Linked Files, Convert To Values & Save To New Folder

May 21, 2008

I have a number of spreadsheets in a folder called country (each sheet is the name of a region). These sheets are linked to another sheet called master which is in another folder.

The regional sheets all have a list of wrap codes and pull details specific to each code from the master sheet. All wrap codes for all sheets are stored in the master, details for wrpas are manually entered here as well as any other associated information.

The link is kept by way of an index match formula used to populate information from the master for all wrap codes in the regional sheet.

Each of the regional sheets act as a report for a specific region and the master is the main source. Every week we need to open the regional sheets (some reside in different folders/subfolders) update the index match formulas in the sheet so that the data is refreshed and then save as a new file with data as values to another folder for reporting.

I am looking for a code that can automatically update all of the excel sheets in a given folder and then save them to a new folder automatically.

I have very very limited knowledge of VB (did make a hello world dialog box once) and am stuck as to any way to automate this process.

View 5 Replies View Related

Open Website - Download CSV File / Save As In Specific Folder Under Different Name And File Type

Jul 6, 2012

I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).

I am successful at opening the website with the following code, but how to do the rest.

Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend

'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub

View 1 Replies View Related

Excel 2007 :: Macro To Open File From A Folder

Oct 19, 2012

I just need a macro to open any excel file may be 2003, or 2007 from a folder.

I did tried Dir but not working. I want the macro in 2007.

View 4 Replies View Related

Excel 2010 :: Placing Pictures From Folder In Spreadsheet

Jan 17, 2013

We updated excel from 2003 -> 2010 and this stopped working.

We have a macro which takes pictures from a folder and pastes those pictures in the excel -sheet.

At 2003 the pictures were correctly pasted to their positions, but at 2010 the pictures appear in somewhere at the same sheet. (not at the right places, which should be at column "o" at the same row.)

Macro (working correctly at 2003) :

Sub Kuvan_piirto(ic As Integer)
On Error GoTo loppu

SourceFile = Enari
DestinationFile = "Live1.JPG" ' Define target file name.
FileCopy SourceFile, DestinationFile
ActiveSheet.Pictures.Insert("Live1.JPG").Select

[Code] ..........

What should I change for 2010?

View 1 Replies View Related

Excel 2010 :: Loop Through To Check For All Filenames In Folder And If Then?

Dec 22, 2013

getting 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 Related

Excel VBA To Open Files In Folder With Specific String In Name Of File?

Aug 29, 2012

I have a requirement to search workbooks in a particular folder with specific string in file name. For example, let us assume I need to find a file which contains the name 'RR' in it. The position of 'RR' will vary with files i.e. 'RR' might be present either in the beginning, middle or at the end of file name. All I wanted is to search for file with 'RR' and do some activity and close the file and then goto next file. Similarly, the next search has to be performed with the files containing the name 'BB' in it.

View 1 Replies View Related

Excel 2010 :: How To Save Excel File As Cell Reference Using Macro

Mar 5, 2012

I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)

Sub aaa()
'
' aaa Macro
'

[Code].....

View 1 Replies View Related

Excel 2010 :: Copy File / Rename And Move To New Folder

Jan 15, 2013

I am trying to copy a file, rename it, and save it to a new folder. I keep getting a "Compile Error ; Syntax Error" at line FileCopy (ImagePath & oldName, NewPath & newName). I am using Excel 2010.

FileCopy (ImagePath & oldName, NewPath & newName)Sub RenameFiles()
'Renames file based on "sheet 1" - Column 1 Old file name - Column 2 New file name
Dim oldName As String
Dim myfile As String
Dim newName As String
Dim ImagePath As String
Dim NewPath As String

[code]....

View 5 Replies View Related

Excel 2010 :: Search Variable Folder And Insert First JPEG

Jul 17, 2013

I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.

View 1 Replies View Related

Excel 2010 :: Retrieve Photo From A Folder And Paste It Into Cell

Apr 20, 2014

Excel 2010, Windows 8.1. I want to retrieve a photo from a folder and paste it into a cell. I have the paste and format process figured out but I'm having trouble putting the file path together to retrieve the photo. If I use the full path, the statement below retrieves "IMAG1234.jpg" and puts it where I want it.

ActSheet.Pictures.Insert("C:UsersEljafeDocuments1-QCReport_PhotosIMAG1234.jpg").Select

But if I have a different photo file name as a string in a cell (e.g. "IMAG5678.jpg") what is the syntax to attach it to the file path? i.e

ActSheet.Pictures.Insert("C:UsersEljafeDocuments1-QCReport_Photos") & "IMAG5678.jpg".Select '(which of course doesn't work)

So, I have a path but I want to attach different photo number to the path from a sting in a cell.

View 2 Replies View Related

Excel 2010 :: Searching A Folder For A Keyword And Returning The Full Path?

Jun 19, 2013

My Excel 2010 spreadsheet contains client data like the below:

A
B
C
D
E

What I'm trying to get from this is a personally addressed email with 2 attachments, one will be standard to all recipients and one will be unique and specific to that recipient. The filename of the unique attachment will contain the reference but will have some other stuff in the filename as well on either side that I will not be able to remove.

In terms of the file locations the unique ones will be in subfolders of the folder holding this workbook and the generic one will be in the same folder as the this workbook.This is some adapted code that solved a similar problem (in Excel 2007 though) on a different website.

VB:
Sub Mail_Report()
Dim OutApp As Object
Dim OutMail As Object [code]....

In that case the file started with the "reference" field but in my case it is in the middle and the formats vary depending on the provider, there will only be a maximum of 5 providers but I would like to avoid 5 different macros if at all possible so I need a search function of some sort.

View 2 Replies View Related

Excel 2010 :: Macro On Multiple Files In A Folder Run Time Error 424

Jun 23, 2013

I have obtained a piece of code from online that runs a macro on multiple files in a single given a single folder. when i implement it though it throws a run time error 424 "object required' and point to 'Documents.Open FileName:=path & file' line of code. Is this because Dim file type is not declared?how do i fix this problem. also, im using excel 2010.A

VB:
Sub Mac()
Dim file
Dim path As String

[Code]....

View 4 Replies View Related

Excel 2010 :: Multiple CSV Files - Macro To Point Folder Where These Saved

May 11, 2012

I'm working on a project where I need to import 150 csv files into an Excel2010 workbook, with each csv file being on a seperate worksheet.

I would like to set up a macro to point to the folder I will have these saved in this up so that each month when I get updated csv files I can repeat the process and bring in the new data. how to create this, I'm very new to VBA?

View 1 Replies View Related

Excel 2010 :: How To Activate File And Create A Folder In Named CustomUI

Jul 28, 2013

I have a Excel 2010 file that I want to activate the file and create a folder in it named customUI.

the steps to achieve this?

View 7 Replies View Related

Excel 2010 :: Rename Files In Multiple Folders And Copy To New Folder

Sep 22, 2013

For the last year I have been downloading cash register X1 and Z1 files onto an SD card. Each night's files (X1 & Z1) are stored in a new folder with the naming convention "RP + [DATE]", (EX: RP120910, [YYMMDD]). I have 265 folders! I need to go through each folder, select the Z1 file (I don't care about the X1, X2 or Z2 files) and rename each with the following naming convention: "Z1 + folder_name + .xls". For Example: Folder RP120910 contains the Z1 file named "Z1_T1729.ECR". After renaming, the file should be "Z1RP120910.xls". As the VBA code loops through each subfolder, selecting and renaming the Z1 files, I would like the new files to be placed into a new folder named "Z1Files". Note: All subfolders are currently in a directory named "RegisterFiles". I am using Excel 2010.

View 3 Replies View Related

Excel 2007 :: Macro To Open A Folder Then Prompt To Allow Manual Selection Of File?

Sep 9, 2013

Its been a while since I did this on 2003 and needing a pointer on 2007.

Just needing a simple macro that opens the relevant folder from a hardcoded path to allow the user to manually select the file to be used.

This file will then be used to copy from and paste to another file.

View 8 Replies View Related

For All Workbooks In Folder - Copy Range To Different Workbook - Save To New Folder?

Apr 21, 2014

I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.

View 2 Replies View Related

Excel 2010 :: Save As PDF Macro?

Apr 10, 2013

I need to create a macro that will allow me to save a PDF from an XL file I am creating by running a report out of Access. This report yields a workbook with 2 sheets in it. The first tab (sheet) called "Mishkon" and the second called "Women's League". I need to save these reports separately as PDFs and they need to be saved in seperate locations. The first sheet (Mishkon) needs to be saved here: \OSOFSDataDayHabMishkon . The second sheet needs to be saved here: \OSOFSDataDayHabWomen's League . Here is the twist... Both sheets need to take their name from their G3 cells. I have Excel 2010 and dont want to print from my Adobe PDF printer. I would like to save as a PDF so I can run it off of computers that dont have the Adobe PDF printer installed.

View 5 Replies View Related

Save / Convert XML Files In Excel Format Without Open Them Into XLS?

Oct 11, 2011

Is there a way to convert all files in folder, in this case, xml in excel format to xls without open them?

I recorded the macro below, but this needs to "Open" and "SaveAs" the current file in folder and there are a few thousands of xml files in folder.

I was wondering if through some ADO or VBA code this can be done without open the files in order to save resources and get the work done faster?

Code:
Sub Convert_XML_to_XLS()

Workbooks.Open Filename:="C:MyPathInputFile.xml"
ActiveWorkbook.SaveAs Filename:="C:MyPathInputFile.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False '
End Sub

View 3 Replies View Related

Open ANY File Type From Excel, Save And Close

Aug 16, 2008

I need to open about 2000 documents one at a time, and save them with a different name.

they are a mix of word documents, PDFs and .jpg pictures.

some VBA like:


Open.file strPath & nextfile
where strpath = C:Temp

and nextfile = picture01.jpg

What should the line be? or is it more complex than a single line ?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved