Excel 2010 :: VBA Macro To Synchronize Windows Folders

Apr 19, 2013

Working with Windows 7 and Office 2010

I've created a Excel workbook with VBA code to synchronize an Access database between a laptop and a server. One of the fields in the database is a shortcut to a jpg for each record. (The Access stuff is not too important here)

Now I'd like to add the ability synchronize the photos between the laptop and the server; so people who use the server can view the photos added by the laptop and vice versa.

Something like:

Code:
For each [JPG] in [LaptopFolder]
If not [JPG] exists in [ServerFolder] then
filecopy [LaptopFolder][JPG], [ServerFolder][JPG]
end if
next [JPG]

That code wouldn't work.

View 4 Replies


ADVERTISEMENT

Close ALL Open Windows Folders

Jul 10, 2012

I use the below code to close ALL open Internet Explorer pages. Im trying to adapt this to close ALL Windows Explorer Folders.

Dim Shell As Object
Dim IE As Object
Dim i As Variant
Set Shell = CreateObject("Shell.Application")
i = Shell.Windows.Count
On Error Resume Next
Do While i > 0
i = i - 1
Set IE = Shell.Windows(i)
If TypeName(IE.Document) = "HTMLDocument" Then IE.Quit

Loop

View 3 Replies View Related

Excel 2010 :: Macros Not Working In Windows 7

Apr 29, 2014

I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:

[Code] .....

Untitled.png‎

View 10 Replies View Related

Excel 2010 :: Macros From Windows To Macs?

May 30, 2013

I developed a tool in Excel 2010 using macros and launched it to a group of dozens of users. Turns out one of them uses a mac and says it is incompatible. I have never used Excel for Mac or tested my tool in it. Next I plan to test it on a mac but how to make this work?

View 4 Replies View Related

Excel 2010 :: Find Out Permissions On Set Of Folders On Company Network?

Apr 30, 2014

I have been tasked with trying to find out the permissions on a set of folders on a company network. I know write vba code to manipulate a excel spreadsheet without any problems, but it is when i'm using objects that it starts to go beyond my capability. whilst searching i found this code:

Code:
Sub test1()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const FullAccessMask = 2032127, ModifyAccessMask = 1245631, WriteAccessMask = 118009
Const ROAccessMask = 1179817

[code].....

It does a perfect job but puts the data into an html file. i managed to get it to input the data into the spreadsheet as well but i would like to clean this code up so that it is not producing another file (namely the text file or the html file) and just put the results into the excel file I have entered the module into.

I did start to remove the references to any file name, but that is where i run into trouble and it wouldn't run any more due to object not found.

I'm on Win 7 using Office 2010

View 1 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

Use Excel VBA To Copy Multiple Files From Different Source Folders To Different Folders?

Jun 12, 2014

I need to back up files, which may be excel or MSword, by copying them from different directories, say C: to the backup directory, say X:. any vba to backup the files? I expect the vba can copy all file listed in column A.

E.g. C: est1File1.xls or C: est2File2.doc, and then pasted to the directory in column B. e.g. X:BackupFolder1 or X:BackupFolder2.

View 1 Replies View Related

Making Macro That Will Create New Folders And Then Save In Those Folders?

May 21, 2014

I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.

As an brief example.

Cell "B3" on the master(worksheet) contains a job name I-40 bridge
So initially I would like the Folder name and file name to be I-40 Bridge

However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.

I am currently working with Excel 2007 Pro edition on windows 8. I

I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.

Public Sub SaveAsA1()
ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub

View 4 Replies View Related

Build Windows 8 App Based On Excel Macro?

Feb 24, 2013

I write a excel file (with macros included) to tackle the datas regarding stock market.

The excel file encompasses some information about financial reports, and some functions (macro) like sorting and filtering.

I'm wondering if I can build it as an windows 8 App and put it on the App market.

Or, in other words, can I make a windows 8 App by the functions in Excel ?

Can I do that ?

View 1 Replies View Related

Using Macro To Print Various Word And Excel Files In Different Folders

Jun 2, 2014

What I want to create is an excel file such as:

A
B
C

1
File
Qty
Yes/No

[Code]......

Where Qty sets how many copies for that particular doc, and Yes/No determines if it needs to be printed at all.

Each file will be in a subfolder relative to the excel file.

So it would work like this (somehow)

If Cell, C2 = 1, then print CurrDirSection1examplefile.xlsx, qty= B2 , defaultprinter
If Cell, C3 = 1, then print CurrDirSection2examplefile.docx, qty= B3 , defaultprinter
If Cell, C4 = 1, then print CurrDirSection2examplefile.docx, qty= B4 , defaultprinter

and so forth.

View 3 Replies View Related

Excel Macro - Open Windows Explorer Window Using VBA?

Jun 6, 2002

What's the code for opening a Windows Explorer window from within an Excel Macro?

View 7 Replies View Related

Create Macro That Will List All Folders / Subfolders And Files In Order In Excel?

Feb 8, 2013

My problem is to create a macro that will list all folders, subfolders and files in an order in excel. So that I know which folders and files belong to which main folder, more like a hierarchy.

For example,
Sourcefolder
Subfolder 1
File1

I have code that list all files in folders and subfolders, but it does not put them in a hierarchy fashion.

View 8 Replies View Related

Excel 2011 :: Mac Recorded Macro Does Not Work With Windows - Runtime Error 1004

Nov 20, 2013

I have been given a macro recorded on a mac and it works fine with Mac Excel 2011. Unfortunately, I need to make it to work on a windows machine, but it comes up with an error message "macro unable to set width property of the window class. Run-time error 1004". When I press debug it takes me to the line .Width = 1456.

Below is the chunk of the code that becomes highlighted when pressing debug.

With ActiveWindow
.Width = 1456
.Height = 795
End With

View 3 Replies View Related

Excel 2010 :: Getting Outlook Warning (2010) While Sending Email Through Macro

Mar 25, 2014

I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.

View 6 Replies View Related

Arrange Or Group Folders Based On Assigned Icons To Folders Using VBA

Dec 20, 2011

I have folders with different assigned folder icons based on the content in the folders.

I would like to arrange these folders in the window based on the assigned folder icons and then by alphabetical order.

How it is to be done. Can we make use of vba having the folders path list in one column of excel sheet?

View 9 Replies View Related

Search Folders And Sub Folders For Worksheet And Return Range?

Jul 8, 2013

macro and I think this has me on my way; however, I do not understand the code and I am having difficulty modifying it. It returns the path in cell $A1 of my workbook, but I get reference errors in $B1:$L1.

I would like for the code to start entering the values in row 2 of my current worksheet. Row 1 I want to make a header row. The first column I like having the file path. Can the path be changed to a hyperlink? I would then like for it to return the values in $AP1:$BC1 to $B1:$O1 of my current workbook.

View 4 Replies View Related

Consolidate Data From Multiple Files Within Folders & Sub Folders

Aug 16, 2009

I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.

The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.

I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.

Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).

Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?

Sub Click2()

Application. ScreenUpdating = True
Dim MCDrow As Long
'Dim SubFolders As String
MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row

Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory
'SubFolders = True
Fname = Dir(Fpath & "*.xls")
Do While Fname <> ""

ThisWorkbook.Sheets("Client Data").Unprotect

Workbooks.Open Fpath & Fname
Worksheets("Client Data").Activate
Worksheets("Client Data").Unprotect

View 6 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 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

Adjust Macro From Excel 2003 To Excel 2010?

Apr 1, 2014

I am trying to adjust the below macro so that it will work in Excel 2010.

Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer

[Code]....

View 3 Replies View Related

Select Folders In Excel Vba

Jun 26, 2014

I want to open 2 or more folders in excel vba how its possible.

View 4 Replies View Related

How To Open Two Windows Of Excel

Jan 24, 2014

Every time I open a new file in excel it opens up the file and the other file I have open disappears. Can I open a file so that there will be two separate windows?

View 3 Replies View Related

Run Macro In Sub-folders

Dec 25, 2010

I have created a macro named "SummaryCalculation" and I want to run this macro to more than 100 excel files which are saved in subfolders in a main folder. Each subfolders have atleast one excel file. I want the macro to be run with in these subfolders not the files are in main folder.

View 3 Replies View Related

Loop Through Folders And Create Folders?

Mar 31, 2014

I want to open C:Projects

and for each Project in Projects, create a new folder with the actual month (e.g. "03" or "12")

Im running the Makro from a file where I'd like to put the month in a cell -> so the code refers to cell("A1") wich has value 01 and creates a folder "01" in every projectfolder in C:Projects

View 3 Replies View Related

How To Traverse Folders And Files On SharePoint With Excel VBA

Mar 9, 2011

how to traverse all files in SharePoint folders using Excel VBA and have not come across an answer I could use because, by configuration design, I cannot map a SharePoint URL to a drive letter.

Here it is:

Sub SPDir()
Dim wb As Workbook
Dim dummyFile As String
'
' The file specified by dummyFile must reside in SharePoint in order to use SharedWorkspace
' The way the code is set up, the path and filename in dummyFile should NOT substitute %20 instead of spaces
' There is logic later to ignore dummyFile on output
' Substitute your own dummy file name below

[code].....

View 9 Replies View Related

Deleting Folders Within A Macro?

Aug 23, 2012

So I have a macro that creates a monthly set of reports, but some of them only relate to period 5 onwards. I wanted the macro to check whether a folder exists for periods 1 to 4, and if so, delete the folder and all the files inside it. I've used the following...

Code:
If Period < 5 Then
If Dir("I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast") "" Then Kill "I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast[code]....

but the macro just skips straight through the secondary IF statements without stopping.

I know this works on deleting files, and presumed it did too for folders, but it doesn;t.

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

Excel 2003 :: Slow Startup On Windows XP SP3

May 4, 2009

I'm currently running MS Excel 2003 on Windows XP SP 3. When I launch Excel from the start menu, it starts right up. When double-clicking an Excel file, it takes approximately 45 seconds to a minute to launch and open the file. This happens no matter what the file size.

View 1 Replies View Related

Excel 2007 :: No Secondary Windows Appearing?

Apr 18, 2013

My Excel gets stuck in a strange world sometimes where it seems like a pop-up blocker is turned on. By this I mean that i can't get question alerts to show in Excel. My only relief has been to reboot.

Here's what I mean - I generally see this two different times:

If I'm in a document that I've modified and click the "X" to exit the file - it never asks me to save, just closes without saving.

When I do a "find" on an item, I do get the first box where I can specify my search criteria. If there is a match, it will take me to the match if I do a find next. However, if I do a find all or there is not match, nothing else happens. I can see the cell addresses flickering in the name box.

View 2 Replies View Related

Launching Excel In Separate Windows As Default?

Aug 16, 2012

Each 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?

View 2 Replies View Related







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