Over 1 million tech questions and answers.

Excel - macro to merge 2 excel files using a common column

Q: Excel - macro to merge 2 excel files using a common column

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx

RELEVANCY SCORE 200
Preferred Solution: Excel - macro to merge 2 excel files using a common column

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?

Read other 3 answers
RELEVANCY SCORE 132.4

Hi all - I need to take 2 different worksheets that have different data, but have one column in common (for ex - serial #) and merge the two in a new worksheet. Any ideas how to do this easily?
 

A:Excel - merge 2 sheets using a common column

Read other 8 answers
RELEVANCY SCORE 106.4

Hi Everyone,

Seriously need your help ><"
I am using MS Office 2010 and Windows 7

I have a data like this (The number of Products and Shops are much more)

I want the result to be like this

This is the attached file for you to download the data or you can simply download from the attachment in this post
http://www.sendspace.com/file/iqvlce

I would really appreciate the help of the community
Kudos to everyone!!!!
 

A:Excel Macro - Merging multiple column into single column

in a new cell, create the following formula.
=A1&" "&B1&" "&C1

Or isn't this what you want?
(the cell-numbers are those which you want to combine).
You can copy the formula for all cells (haven't seen your file, find the link a bit tricky, maybe attach the file in a post here)
 

Read other 3 answers
RELEVANCY SCORE 99.2

I have to download spreadsheet on a weekly basis and I have to delete some column however the column that I need to delete always the same name but not located always in the same position (i.e. column tagline sometimes in column D sometime in Column F) Can someone help me create a macro that with delete the column using the name instead of the column Letter.

Thank you

Q
 

A:Excel Macro to delete column

Read other 7 answers
RELEVANCY SCORE 99.2

I barely use Excel but I have a list to modify / reformat.

What I have is about 25,000 names and addresses in one column:

address 1
_________
name1
_________
address 2
_________
name2
_________
address 3
_________
name 3

what I want to achieve is 2 columns where one is address , other is names

__________________
address 1 | name1
__________________
address 2 | name 2
__________________
address 3 | name 3
__________________

etc .

Can you help me out with macro to move every 2nd cell / row in first column and move them to next column , like it's shown in example above?

thank you for any help
 

A:Help! Excel Macro: How to move every 2nd row to next column?

Read other 6 answers
RELEVANCY SCORE 98

I need an excel macro to refer to a different column each time I run the same macro. Does anyone know what the syntax is for this?
 

A:How do you get an excel macro to refer to a different column each time its run?

Read other 14 answers
RELEVANCY SCORE 98

I am setting up a series of Excel sheets for my boss, in which one tab gets data from a previous tab. What I am trying to do is find a way for this new column (in the second tab) to be automatically sorted (it is a list of email, so alphabetically). This would than feed into another Excel sheet. I don't want my boss to have to go into that intermediary sheet and click sort himself or have to run a macro. Basically, is there any way to sort an entire column by utilizing formulas. I don't care if I have to create one or many extra columns. Thank you very much. Dan
 

A:Excel Help - Can you sort a column by using formula (not macro)

I may not be on the right track here, but for me, and my technically challenged bosses, the easiest thing to do is to setup autofilter. if you are using MSOffice 2003 or later it allows you to sort ascending or descending order by clicking the drop-down arrow and scrolling up on the options and selecting the sort order, this also allows for custom sorts, ie you are looking for only the contact details of all the members of a certain company. when i first started with this it seemed awkward but over time even my bosses have come to enjoy this particular feature of MSOffice 2003 and later. the older versions don't allow you to do the sort function on the auto-filter button. otherwise write a macro that runs when you open the spreadsheet.
 

Read other 3 answers
RELEVANCY SCORE 97.6

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

Read other 1 answers
RELEVANCY SCORE 97.6

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

A:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

Read other 1 answers
RELEVANCY SCORE 97.2

I'm using some software to export payment information into a CSV file. This file needs to be in the bank's specified SIF format which requires all the data to be in one column.

The limitation with my software is that the detail of each payment gets exported into it's own separate column. For instance, cells A1:A10 contain payment details to one individual, B1:B10 to another, and so on. I require the whole lot combining into Column A, running from A1:A65536 (it will never go this far down the spreadsheet, but you get the idea).

I've tinkered with some basic copy and delete macros but I'm unable to make the headway that I need to. I don't have the touch when it comes to programming .

Thanks in advance
 

A:Solved: Excel: Macro to combine all Columns into Column A

Read other 12 answers
RELEVANCY SCORE 97.2

In a database exported to Excel, a number of records come in for multiple sales people. Each row is a record. However the sales person's name in Column A is only shown once and for each of his records below that, the cells in column A are blank until the first record of the next salesperson, where his name is shown only once, then all his records are shown in subsequent rows, but cells below HIS name are blank (they do not repeat his name) until the first record of the next salesperson, and so on.

In order to sort these records I need to autofill the blank cells below EACH salesperson's name down to where the next salesperson's name first appears, and then autofill the next salesperson's name down to the next one, etc.

This step is part of a larger macro, but I have to end the macro there and do this manually because I can't figure out how to write the code to do it automatically. ??? Hope someone can help!

Thanks, Jim
 

A:Solved: Need Excel Macro Code to Autofill Column

Read other 12 answers
RELEVANCY SCORE 97.2

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

Read other 2 answers
RELEVANCY SCORE 97.2

I am trying to write a macro in Excel... it's fairly simple, but I can't figure out one part. I used the record feature to get started (I'm quite rusty in writting macros)...

What I want to do:
I have lots of data in one worksheet. I want to pick out some of it and put in into another worksheet. I want the row to be variable, the columns set.

For example, I want the info from row 26. I want take the info from worksheet "pricing" cell F26 and put it in worksheet "report" in cell B5.

Excel gives me:

Range("B5").Select
ActiveCell.FormulaR1C1 = "=pricing!R[21]C[4]"

For whatever reason, using the record feature, it's selecting the row and column in reference to the cell that the data is going in to! (B5 + 21 rows + 4 columns = F26)

What I would like is it to pull info from a variable row and fixed column... can you help me with the scripting? I know how to set the variable and such.
 

Read other answers
RELEVANCY SCORE 97.2

Hello I'm having a problem in create a excel macro, that multiply the values from a table(for example the range is (B5:L20)) by the values that are in the column (A5:A20). The result should be place in another sheet.

There is someone who can help me, please.

Regard
 

A:Solved: Excel Macro multiply range for a column

Read other 6 answers
RELEVANCY SCORE 97.2

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

A:Excel Macro to Move data in Rows to column

Read other 8 answers
RELEVANCY SCORE 97.2

Hi,

I've used Tech support guy for quite a while and almost always find what I need.
I guess it's about time I join and post a question

So this is my problem.
I send out an excelreport every day.
In it I use the content in column X to populate the hyperlink address in column Y.
I've done this manually but now the report is getting bigger and a macro seems in place.
Problem is I just don't know enough about the syntax used in excel.

What I want to do is loop though each row - if there is data in column Y then check in column X.
If X is populated use the data as hyperlink address in column Y (info in Y should still be what is shown).
This should be done for each row where Y is populated.
Then the X column shall be deleted (but I guess I could do that manually )

It feels pretty straight forward and I could do it in RPG or SQL but the syntax in excel befuddles me
Any tips?
 

A:Excel macro - use info from one column as hyperlink address in another.

Assume Y2 and X2 cells
=if( Y2 = "", "", IF(X2="", "" , do Y2)

but you need to have the formula in Y and change Y - i think that may need a macro, as you are testing Y and also rewriting Y

a spreadsheet with dummy data would be useful to see
 

Read other 1 answers
RELEVANCY SCORE 96

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

Range("A1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?
 

A:Solved: Excel macro to select data in a column with a header row

Dim lastRow as long
LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select
Or if you need more columns then Range("A2:H" & lastRow).select
 

Read other 3 answers
RELEVANCY SCORE 96

Hi All,

I have a list of over 20 or so web sites and their corresponding advertisers and how much money they spend with each web site.

The list of web sites looks like this:
Weather Channel............Dollars..............About.com.......Dollars......etc...for 18 columns
IBM...............................$200,500..............Cingular...........$350,000
TMobile..........................$450,000..............Revlon............$275,000

My goal is to consolidate all the advertisers into a SINGLE COLUMN then to the right of it, have all my web sites across as COLULMNS reporting.

should look like this:

............................Travelocity.............. Expedia ....................Orbitz
ADVERTISERS
AT&T Corp...............$78,100................$94,700....................$271,200
Starwood Hotels.......$696,500...............$30,600....................$124,100
Hilton Hotels............$306,700................$900........................$64,200
Ramada...................$13,000.................$450,000...................$600,000
etc....(for at least 150 rows)

I'd like to be able to have the flexibility to add as many columns (Web sites with advertiser dollar amounts) as possible but have the macro paste the result to a NEW worksheet.

Does anyone know how to do this?

-bob
 

A:Consolidating list of web sites and their vendors into one column - EXCEL Macro

Read other 11 answers
RELEVANCY SCORE 96

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
aaa------bbb---------------------$55
ccc------ddd------$44--------------------------$66
To this:

col1------col2------category------amount
aaa-------bbb------2-----------------$55
ccc-------ddd------1-----------------$44
ccc-------ddd------3-----------------$66
I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help
jeannie
 

A:Excel macro - change column data to multiple rows

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub

HTH (welcome to the board)
 

Read other 2 answers
RELEVANCY SCORE 94.8

I have the following macro below which works great for when any value is entered in Column C, it time/date stamps the adjacent cell in Column D. However, I need it to do the same thing a few columns over (ie, when any value is entered in Column H, it will time/date stamp the adjacent cell in Column J)

For the life of me, and cannot get it to repeat . Any ideas? I'm a bit new at this, but thanks in advance for the help. Feel free to spell it out - I won't be offended.

Thanks! Here's the current macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C3:C9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

A:Solved: Excel Macro - Repeat Time/Date Stamp for Different Column?

Read other 6 answers
RELEVANCY SCORE 94.8

Hi Guys,

I need help creating a dynamic macro that could save me alot of time in the future.

In a nutshell:

- "Name" value in Column A
- "Yes" or "No" value in Column B
- "Name" value in Column C

I need:

- Every cell in Column C to be crossed referenced with Column A
- If there is a match, I need the value in Column B to be changed to Yes

Before:

------Column A ------------------------------ Column B ------------------------------ Column C -------
-------------------------------------------------------------------------------------------------------------------------
-- Jen NO Jen --
-- Jane NO Jane --
-- Sarah NO Sarah --
-- Mike NO James --
-- Mitch NO Fred --
-- Joy NO Lee --
-------------------------------------------------------------------------------------------------------------------------

After:

------Column A ------------------------------ Column B ------------------------------ Column C -------
-------------------------------------------------------------------------------------------------------------------------
-- Jen Yes Jen --
-- Jane Yes Jane --
-- Sarah Yes Sarah --
-- Mike NO James --
-- Mitch NO Fred --
-- Joy NO Lee --
-------------------------------------------------------------------------------------------------------------------------

Thanks for the help,
Ben
 

A:Need Help - Excel 2010 - Macro - Find, if Found Replace Text In Another Column

As just a Formula, put this in column B ' =IF(A1=C1,"Yes","No")'

As a Macro -
Sub MatchReplace()
Dim LastRowColA As String
LastRowColA = Range("A65536").End(xlUp).Row
For i = 1 To LastRowColA
Range("B" & i).Select
ActiveCell = "No"
If ActiveCell.Offset(0, -1) = ActiveCell.Offset(0, 1) Then ActiveCell = "Yes"
Next
End Sub
 

Read other 2 answers
RELEVANCY SCORE 94

Ok, just a bit of advice needed on this one for the time being...
I need a macro that will:

- Save all (Excel) attachments from the currently open email message (or a message selected in the Inbox) to a directory on the PC.

- Merge all the saved Excel files into a single file - they're all formated exactly the same - data in about 20 columns, no column headers or anything, just standardised data.

- Save the merged file with a standard name (i.e. every time this is ran, it needs to have the exact same filename).

- Delete all the files in the directory apart from the merged file.

I know there's probably a fair bit of code online for doing the individual parts of this. Can I do both from within Outlook? It doesn't particularly matter if I need to do this in 2 steps - an Outlook macro to save the attachements, and an Excel macro to do the file merge etc.

Cheers.
 

A:Solved: MS Outlook / Excel macro - extract attachments and merge

Read other 16 answers
RELEVANCY SCORE 93.2

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

Read other 3 answers
RELEVANCY SCORE 88.4

I have column (i pretend column is line which goes from up to down) . It contains values. like 450 , 320 , 14000 . I want to delete entire row ( left to right) , whose value is less than 900. how cna i do this?

thanks

http://imageshack.us/photo/my-images/818/was34.png/
 

A:Need to edit excel files column

Read other 9 answers
RELEVANCY SCORE 87.2

I have 2 excel files. I want to merge them together. It wil be like 100 excel files. Something like

bus
bus1

car
car1

laptop
laptop1

How can i merge 100 files together using macros.

thanks
 

A:how to merge 2 excel files

Read other 16 answers
RELEVANCY SCORE 86

I was wondering if it is possible to merge multiple excel files into one?

I have the following:

name1.xls
name2.xls
name3.xls

etc... it goes on for a while and I want it so that when I open

name1.xls it has name2.xls, name3xls etc... where sheet1,sheet2 & sheet3 is etc...
copying all the data manually would take ages so is this possible?
 

A:merge multiple excel files into 1?

Read other 13 answers
RELEVANCY SCORE 85.2

I'm trying to Merge an Excel spreadsheet to Outlook and provide alerts for critical dates in said spreadsheet in the Outlook calender !
 

A:Excel - Merge Excel spreadsheet to Outlook with Alerts

Welcome to TSG faithtronic.

I've never done it but your thread has been setting here a while. A method to do this here http://www.ehow.com/how_5685419_create-calendar-excel-data.html

I would start with a couple of dates so not to screw up the whole calender! There are some sharper excel folk on this site that might provide a better answer. But this is a starting point?
 

Read other 1 answers
RELEVANCY SCORE 83.6

Hi,

I have a problem in merging two worksheets in excel as the two sheets have different no of rows in it. and i need to merge the two sheets in a new worksheet, and in that work sheet i need to remove the duplicate values present in the both work sheets.

anybody can help me to do that.
 

A:Need to Merge two excel sheets in one excel sheet.

Hi,

What version of Excel are you using. If it's 2007 then I think you can copy the data to another worksheet and select the range then use the "Remove Duplicate" option that you find it the "Data" tab.
 

Read other 1 answers
RELEVANCY SCORE 83.6

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 83.6

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

Read other 1 answers
RELEVANCY SCORE 83.6

Im using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers
RELEVANCY SCORE 83.6

I have a list of 39525 emails in column A and a list of 19909 emails in column B. If an email exists in column B that also is in column A I want it removed from column B. PLEASE HELP! THANK YOU!!!!!!
 

A:Solved: Excel 2010 - Remove duplicates from column B that exist in column A

Read other 9 answers
RELEVANCY SCORE 83.2

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

Read other 1 answers
RELEVANCY SCORE 83.2

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....
Example:
Everytime column a = "Jones", check if column e = "tom".....if so count it.
 

A:Counting strings in one column based on Condition in another column in Excel

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

Read other 3 answers
RELEVANCY SCORE 83.2

Hi Rollin_Again and everyone in this forum,

Hoping someone could help me in my problem.
I have a workbook in Microsoft Excel 2010 (Windows) of about 60.000 rows of words in different languages.

Some words have 5 different languages and some are only 4 or 3, all are written in Column A of the workbook.

Column A
English -------(in color Orange)
Spanish -------(in color Red)
Italian ------- (in color Blue)
German ------ (in color Black)
Papiamento ---(in color Green)
etc.

What I want is a Macros Program that can put all Spanish words (red color) in Column B, all Italian words (blue color) in Column C, all German words (black color) in Column D, and all Papiamento words (green color) in Column E.

Like for example:

Column A --- Column B ---Column C --- Column D ---Column E
English -----Spanish ----Italian ----- German --- Papiamento
etc.

Thank you very much in advance and I'll appreciate all the helps you could give me.
sukisuki
 

A:Transfer texts from Column A to Another Column, Excel 2010 (Windows)

Read other 9 answers
RELEVANCY SCORE 82.4

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

A:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

Read other 1 answers
RELEVANCY SCORE 82.4

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

A:Excel Macro runs fine..then excel crashes

Read other 11 answers
RELEVANCY SCORE 82.4

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

A:Excel Macro - converting excel to word doc - stops after row 29

Read other 13 answers
RELEVANCY SCORE 82.4

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

A:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

Read other 1 answers
RELEVANCY SCORE 82.4

I have a spreadsheet that I would like to give to both English and Spanish speaking workers. Is there a way to convert the English text in column A to Spanish in column B? I will be changing the text in column A on a weekly basis and will need to translate then distribute to all workers.

Thanks in advance.
 

A:Excel - Translate English in Column A to Spanish in Column B

Read other 8 answers
RELEVANCY SCORE 82.4

COLUMN A
5
5
6
9
COLUMN B
A
A
B
B

A=10
B=15
Hi there, I need help creating a formula that adds Column A, only if Column B = 'A'.
I then need to multiply that counted value by another cell.

Can someone please help me? I think I'm over-thinking this.
 

A:Excel: Count Values In One Column On Basis Of Other Column Value

=SUMIF(B1:B4,"A",A1:A4)

Where B1:B4 is where your A and B values are, and A1:A4 is where your numbers to sum are located.

HTH
 

Read other 1 answers
RELEVANCY SCORE 82

Hi Again

Keballah gave me this macro that when I put the cursor in a cell that has for example WW1.xls and then execute this macro it opens up the Excel workbook named WW1. However, when I try to open a PDF file like WW1.pdf I get garbage and not the PDF file. Can someone out there take a look at the macro below as an example and create a new one so that it also brings up PDF files??

Thanks

Mario


Sub Demo()
Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim Mask As String
Dim thisFolder As String
Dim fullFilename As String

Set fso = CreateObject("scripting.FileSystemObject") ' late binding
'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

Set fldStart = fso.GetFolder("M:") ' <-- use your FileDialog code here
Mask = ActiveCell.Value
thisFolder = fldStart & IIf(Right(fldStart, 1) = "\", "", "\")
Debug.Print thisFolder, Mask
If Not IsError(Dir(thisFolder, vbReadOnly)) Then
If Dir(thisFolder & Mask, vbReadOnly) <> "" Then
Workbooks.Open Filename:=thisFolder & Mask
Else
fullFilename = ListFolders(fldStart, Mask)
If Len(Trim(fullFilename)) > 0 Then
Workbooks.Open Filename:=fullFilename
End If
End If
End If
End Sub

Function ListFolders(fldStart As Object, Mask As String) As String
Dim fld As Object 'Folder
Dim thisFolder As String
For Each fld In ... Read more

A:Hyperlink Macro in Excel VBA that finds PDF files

You're getting garbage as you're asking excel to open a pdf as a workbook:

Code:
Workbooks.Open
You'd need to open the PDF using a PDF viewer and code accordingly.
This might be helpful:
http://www.myengineeringworld.net/2012/07/vba-macro-to-open-pdf-file.html
 

Read other 2 answers
RELEVANCY SCORE 82

Hey all,

Im currently working on a macro of rather large size and im a little stuck at the moment.
The endgoal of the macro is to have a clean file out of all the mess and that can be mailed to other pple of my staff. Now here it comes:
In my example there are 3 tabs: Raw file, Database and Endfile. The point of the whole macro is to look up some things between Raw file and Database and then to write it to the Endfile. The Raw file and the Database are allready macromade and all three sheets have a layout that cant be changed.
--> First thing is that i need to check if the Rapprt N in column L of the Raw file is somewhere in the Database file at column M (CINCUS).
--> IF the number is there I need to check the type wich can be found at column S (CINSTA)
--> IF THEN that type is 7 then it has to be written away to Endfile, what has to be written away is colored blue in my example.
--> IF THEN that type is 8 it has to be written away to the sheet "Type 8" for further investigation, the data is the same (blue colored)
--> IF the number is not found it has to be written away to the sheet "Not Found" with also the same data.

I think thats it, its much i know. But im stuck in finding all the formulas and stuff. So some help is greatly appreciated. Thx in advance.
 

A:Macro excel matching and writing files.

Read other 9 answers
RELEVANCY SCORE 82

Hi all

Excellent site! I know I should check for duplicate queries, but couldn't find the search feature as I'm new here and this is my first ever post...

I have two files 'Expenses Analysis' and 'Expenses Form' whereby the user fills out his / her expenses in the analysis file and then transcribes the summary figures into the form on other file, which is then sent off to the finance department.

I've created a simple macro that transcribes the figures between the files when they are both open, but I want it to be clever (=more fool proof) than that. I would like to embed the Expenses Form file into the analysis file, and get the macro to launch the file and enter the figures in one go. (I need to do it this way, rather than combine the functionality, as the Expenses Form is owned by another department and I can't unprotect it etc... ).

Sounds very simple, but I've trouble finding resources on it, as it seems people misuse this functionality for viruses and the like . If another could help me, I'd be much obliged!

I would like to find a way of launching Expenses Form from the Expenses Analysis file, bearing in mind that both files contain macros and a security warning pops up in between.

Kind regards

Neil
 

A:Opening embedded files in Excel using macro

What application are you using? Can you post some sample files?

Rollin
 

Read other 3 answers
RELEVANCY SCORE 82

Is there a way to have Excel open a folder and place the file name with extensions in Column A. Then open another folder and place the contents of that folder into Column B?

I also would like the list to skip adding file to column if it already exist on worksheet, but if a file was deleted from folder it would be removed from the worksheet.
 

A:Excel 2007 Macro/Folders & Files

Hi

Here is a macro that I use quite often ...
Code:


Sub Get_File_Names()

[B]'1st directory[/B]

lRowA = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lRowA).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 1) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 1) = MyFile
Wend


[B]'2nd directory[/B]

lRowB = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1:B" & lRowB).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 2) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 2) = MyFile
Wend
End Sub

Unless identifying which files are new and what's been deleted is important I would just delete the previous list and get all current files - as I've done in this code.
 

Read other 2 answers
RELEVANCY SCORE 82

Good day,

I have an inquiry, I wanna ask if there is a way to get the contents of .txt #1 file and place it in a sheet in excel starting from cell A2, then the macro will look for the end of the copied .txt file in excel...offset it by 3 cells down then paste the contents of .txt #2.

Then the macro should also be able to delete the pasted values from .txt #2 and replace it with .txt #3 at any time should the user choose to do so still making sure that it's below .txt #1.

The data is actually being used by a pivot table, it would just refresh everytime we have a new .txt file coming in without replacing the first part. Oh...i don't have Access on my system, I only have VBA in Excel and the text files to use.
 

A:Solved: Text files and Excel Macro

Read other 6 answers