Over 1 million tech questions and answers.

Excel: Automatically import data from another workbook

Q: Excel: Automatically import data from another workbook

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you can

Read other answers
RELEVANCY SCORE 200
Preferred Solution: Excel: Automatically import data from another workbook

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.)

RELEVANCY SCORE 129.6

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you can
 

A:Excel: Automatically import data from another workbook

bparker said:

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you canClick to expand...

Hi bparker - Not sure I'm reading your intent right, but if you can save the "19991118damlbmp_zone" etc sheets into a book on the desktop you name "Book11" and then open a new book called "Book10" and run the code from Book10 - you can use something like this:

Code:
[SIZE=2]Sub COPYIT()
'this is run from the book named Book10
Dim N%
Application.ScreenUpdating = False
On Error Resume Next '<< error is - "books already open"
Workbooks.Open Filename:="C:\WINDOWS\Desktop\Book11.xls"
For N = 1 To Sheets.Count
Workbooks("Book11.xls").Activate
Sheets("Sheet" & N).Select
Range("A1", Range("A65536").End(xlUp).Rows.EntireRow).Select
Selection.Copy
Windows("Book10.xls").Activate
Sheet... Read more

Read other 3 answers
RELEVANCY SCORE 88.4

Hello,
I have a master workbook with 20 worksheets. 5 worksheets in the worksbook are distributed to field reps, but the remaining 15 worksheets are not. What is the best practice for receiving the 5-worksheet workbook from the field rep and importing the data into the 20-worksheet master workbook? If I merely use Copy Sheet I am able to copy the worksheets into the master workbook but then I have to remove the pathname references in each of the copied worksheets in order to map the data in the master workbook - which is quite cumbersome. Suggestions?
 

A:Populating data from one Excel workbook to a master workbook

Hi jpirhalla, welcome to the forum.

Have you tried moving the sheets over. Dragging the sheet from the field reps back into the now-15-sheet book?
 

Read other 2 answers
RELEVANCY SCORE 80

I have the follow code behind the ThisWorkbook_Open Event:
Code:

If Weekday(Date, 1) = 6 Then
'Do nothing
Else
ThisWorkbook.Close
End If

Is this the best way to perform this operation?

Also, if the day was not yet Friday, how would I keep the workbook from closing if I needed to open it for modifications?
 

A:Solved: Excel VBA Automatically Close Workbook

Read other 8 answers
RELEVANCY SCORE 77.2

Hi

I was wondering if the above can be achieved?
My scenario is I have an issues log and the file name is saved as the issue title is there a way to get the saved file name into another workbook appearing as a hyper link back to the original?

I am assuming VBA will be needed and added on to the code below which i already have, the code takes the value of C9 (the issue title) and saves it in the right folder.
Code:

If Range("C9") = "" Then
Msgbox "Please ensure you fill out the Issue Title. Thanks"
Else
Saveactiveworkbook.saveas "C:\filename\filename\ & Range("C9")"
Msgbox " Thank you, your issue has now been saved."
Endif
 

A:automatically populate saved excel document as a hyper link in another workbook?

Read other 10 answers
RELEVANCY SCORE 75.2

I have been given a last minute assignment to create a survey to send to a few end users and I want to compile the data of all the forms I receive into one excel workbook. I tried a few macros, but they aren't 2010 macros and so I am running into issues; I just don't have to time to re-write and could use a little assistance. I think the main difference is that I am using radio buttons instead of text fields.

ALSO I want to automate the entire thing. Meaning I want to open the workbook and run the macro to pull unprocessed forms from one folder, scan for data, import data, and then move them to another folder for archiving.

Also, in my form I don't think I made the comments section into a text field; I am sure I am going to have to correct that in order to capture the data... Right now I am too tired to think on it. At the end of each section I there is a comments field and I want to collect that data onto different sheets in the workbook. I already labeled the sheets.

I have attached the drafts for reference; I only shortened it for upload; the original draft has about 30 questions total. I need to get this done by 1/15/15 so that I have time to receive and compile data by 1/20/15.

Please help!
 

A:Export Word Form Data into Excel Workbook

Read other 7 answers
RELEVANCY SCORE 74.8

Hi there,

I don't have enough experience in VBA excel or word, and would you mind helping for the following:
Q1
The below macro code add row below the last row. And Would you mind telling me what is the code that can add row for word document table between the last row and the row before the last?

ActiveDocument.Tables(1).Rows.Add

Q2:
My second question is related to export/import between two different programs or files ( Excel and word document)
Would you mind providing me the proper code for
Wordtable.copy
Workbook.xls - sheet1.past speical value
Suppose the word tables excel workbookD sheet 1 are as follow

Table 1 in word document
--------------------
Cell 11 - Cell 12
Cell 12 - Cell 22
--------------------
Table 2 in Word document
------------------------------------
Cell 11 - Cell 12 - Cell 13
Cell 21 - Cell 22 - Cell 23
------------------------------------

WorkbookD.xls – Sheet1
Rows Column
A B C D
1
2 wt1.Cell 11 WT2.Cell 11 WT2.Cell 12 WT2.Cell 13
3 WT2.Cell 21 WT2.Cell 22 WT2.Cell 23

How I write a macro to make the value or text in Cell 11. in word table 1 to be copied and past in Cell A2 in sheet1 of WorkbookD.xls
and the value or text of the range from Cell 11 to Cell23 of table 2 or word.doc to be copied and past in sheet1 of WorkbookD.xls from range B23 for n rows in table1 and table 2 of word.doc.

Thanks

Hamdy"
 

Read other answers
RELEVANCY SCORE 74

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 73.6

I have a workbook with data for all employees in it. Once a month I need to pull out a copy (certain columns) of the employee data that meet a certain criteria and paste it into another workbook for manipulation.

pull out all rows that have 513, 535, 540, or 560 in the "dept" column and paste it into another workbook. I am attaching a sample of each workbook.

1-testing macro contains all emaployee data
2-Copy into workbook (self explainatory)

I don't write vb code, but I can definately copy, paste and do some editing. Any help would be greatly appreciated. I have been searching online post all day, but have been unable to find anything I think is similar, at least that I could understand.
 

A:Solved: copying select data meeting criteria from one excel workbook to another

Read other 11 answers
RELEVANCY SCORE 71.2

Hi,

how to download or import data from website to Excel automatically? is there any software's for that please provide me information and assist me

regards
satya
 

A:Import data from web to Excel?

Read other 6 answers
RELEVANCY SCORE 71.2

Hi,
Apologies if this has been asked before.I have two different workbooks which I need to link I need to set up an arrangement whereby the data from a particular cell in one workbook is displayed in preferably pop up box in another workbook.
I have attached dummy versions of both the workbooks below.
1.The spreadsheet titled "Issues Log" has a list of issues,each with a unique identifier(column A) which is linked to a unique cab no (Column B).A brief description of the issue is given in Column C.
2.The spreadsheet titled "Link to Display Spreadhseet" has a list of CAB numbers (Column A) which corresponds to the "Issues Log" but the order is jumbled up.
In column B ,I need to place a link which will display the "Unique Identifier" code from the "Issues Log" corresponding to the cab number.When a person clicks on this Unique ID code ,one of the two things should happen: i.either the breif description of the issue (Column C in the "Issues Log") should pop up in a box.or
ii.Clicknig on the link will open up the issues log.

I hope I am clear in what I want.i am ok with using long winded formulas,tried some combinations of Match,IF etc but always hit a brick wall.I am not good with Macros,VBA.Any help/pointers would be greatly appreciated.

I am using Excel 2007
regards
 

A:Help needed to display data from one workbook in another workbook in a pop up cell

Hi,

Just a question, since you need to know what is the issue with its description. why do need the unique identifer for that and linked it do description. It might be a silly question, but please forigve me for that.

In case you don't need the unique identifier column, you can use vlookup for the populating the details.

Regards,
Tushar
 

Read other 3 answers
RELEVANCY SCORE 70.8

Hello Everyone

I am having some difficulties finding a solution to my problem. I am hoping someone can help or lead me to the right direction.

I have a workbook called Data.xlsm, below is a sample:

ID, Name, commission
1 john 1000
2 eric 2000
3 ryan 3000

Now I have another excel file called Temp.xls which looks pretty much the same as Data.xlsm.

ID, Name, Commission
1 john 200
2 eric 300

I need a macro that will read the Temp.xls and grab the commissions and bring them to Data.xlsm. The commissions need to be summed based on ID.

The final Result on Data.xlsm should be as follows:
1 john 1200
2 eric 2300
3 ryan 3000

Any help would be greatly appreciated.

Thank you
 

A:Solved: Excel Macro - Upload Workbook value from Another Workbook based on ID

Just a quick and dirty solution

Make sure you place this in the same folder as your Temp.xlsm file and press the button

It will open Temp
update the data, if a new Id is in Temp it will add the data and set the commission values in Temp to 0 to avoid running it twice.

I hope the code explains itsefl.

Think of a macro of a step by step instrcution as you would say it out loud.
 

Read other 3 answers
RELEVANCY SCORE 70.4

Hi All,

I have a situation where I need to take data from this format
NAME
Clown, Bozo (8430)
Bunny, Bugs (2940)
Runner, Road (5023)

(basically in column 1 we have a list of last name (comma) first name and in parenthesis the last four of their SSN.)

I want to move the data from that sheet into another where the format is
First Name Last Name
Bozo Clown
Bugs Bunny
Road Runner

I would like the data imported to this new sheet sheet with the first name and last name in two columns instead of one (delimited by comma) and no last four, I want it in this format so I can mail merge that data into a word doc (I already know how to merge) but I don't know how to import the data to have the first and last names separated by columns with no last four of their SSN.
Can anyone help?
- Thanks
 

A:Excel data import problem

Hi there,
Try this macro
Code:

Option Explicit

Sub SplitNames()
Dim s1 As Worksheet, s2 As Worksheet
Dim p1 As Integer, lstRow As Long, xrow As Long, iRow As Long
[COLOR="Red"]Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")[/COLOR]
lstRow = s1.Range("A" & Rows.Count).End(xlUp).Row
iRow = s2.Range("A" & Rows.Count).End(xlUp).Row
If s2.Range("A1") = "" Then s2.Range("A1") = "First Name": s2.Range("B1") = "Last Name"
For xrow = 2 To lstRow
p1 = InStr(1, Cells(xrow, "A"), ",")
p1 = p1 - 1
If p1 > 0 Then
iRow = iRow + 1
s2.Cells(iRow, "B") = Left(Cells(xrow, "A"), p1)
s2.Cells(iRow, "A") = Mid(Cells(xrow, "A"), (p1 + 2), InStr(p1, Cells(xrow, "A"), "(") - (p1 + 2))
End If
Next xrow
End Sub

Just check the two lines in Red to see if your sheet's names exist, else change them accordingly

The Instr() function determines in the first case where the , is and in the second place to determine the (

The , and ( must be presnt for this to work.
 

Read other 3 answers
RELEVANCY SCORE 70.4

I am trying to do a web query on http://www.tntexpress.com.au/interac...imes_tntau.asp with information entered into the text boxes and the result into excel.

I have tried using a macro (in the workbook vba of excel) but it failed and I'm not sure if I am supposed to use post or some other way for text boxes :

Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "http://www.tntexpress.com.au/interac...esX_tntau.asp?" 'DONT FORGET TO COPY LINK LOCATION BECAUSE TECH SUPPORT GUY SHORTENS THE ADDRESS
Const PostOSuburb As String = "txtOSuburb=CRANBOURNE"
Const PostOState As String = "&txtOState=VIC"
Const PostOCode As String = "&txtOPcode=3977"
Const PostDSuburb As String = "&txtDSuburb=ASPENDALE"
Const PostDState As String = "&txtDState=VIC"
Const PostDCode As String = "&txtDPcode=3195"
Const Postcolmonth As String = "&colmonth=December"
Const Postcolyear As String = "&colyear=2008"
Const Postcolhour As String = "&colhour=15"
Const Postcolmin As String = "&colmin=00"

MyPost = PostOSuburb & PostOState & PostOPcode & PostDSuburb & PostDState & PostDPcode & Postcolmonth & Postcolyear & Postcolhour & Postcolmin

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MyUrl, Destination:=Cells(1, 1))
.PostText = MyPost
.BackgroundQuery = True
.TablesOnlyFro... Read more

A:Import Data from website to excel using vba

Please don't post duplicate threads. Continue the problem resolution here: http://forums.techguy.org/business-applications/782450-excel-web-query.html
 

Read other 1 answers
RELEVANCY SCORE 69.6

I've got a macro set up on a worksheet in workbook A, which once an hour is exporting data to a worksheet in workbook B. This is housed on a shared drive. I'm trying to set up Workbook C to have a worksheet setup that imports the data from workbook B, so that management can view hourly updates without having to check their email. I've set a query on workbook C to refresh at regular intervals to pull the updates each hour. The problem is that once workbook C refreshes, it locks workbook b preventing any further writes from workbook A. I've tried setting longer intervals between the refreshes, setting the workbook B as shared, and attempted to change the Mode in the OLE QUERY from Share Deny Right to Read.

TL;DR -Basically it boils down to the auto-refresh on workbook c maintaining a connection to workbook b, preventing workbook b from being updated. Is there a way to only have workbook C connect to workbook b during the refresh, instead of maintaining a constant connection as long as auto-refresh is enabled?

Any help is greatly appreciated.
 

A:Excel Import External Data help needed!!

Read other 7 answers
RELEVANCY SCORE 68.8

I have run into an interesting problem here... We run an AS400 server from which data is imported into excel 2003 format for billing and other items... We have a user that has run in to a problem. Any X-fers of data in excess of 16000 lines will cause a statement from excel stating that the data will not fit onto the worksheet. As some may or may not know, the current limitation of 2003 is ~ 65K lines. The CPU itself is not an issue, or atleast should not be an issue P4 2.26 256 RAM... ideas?
 

A:Excel 2003 Data Import Limit 16K lines?

Can I ask?
How is the data imported?
From File-Open with Excel?
Or opening a CSV?
Or what?

Are you absolutely certain that an older version of Excel is not being launched when he opens the file?

If the type to be saved under Tools-Options-Transition just "Excel workbook" or some lesser version (tho I don't know if it would cause this issue).
 

Read other 3 answers
RELEVANCY SCORE 68.8

I have tried using a web query for what I am trying to do, and it just wont work. So I am wondering how I can copy an open webpage to excel automatically.
 

A:Solved: How do I import data from a webpage that is open to excel?

Read other 7 answers
RELEVANCY SCORE 68.8

Hi there,

I'm trying to import data from an Excel spreadsheet into a Word document that already has the mail merge set up in it. Whenever I try to do this, I select the source after which it just sits there with a message in Word indicating 'Initiating DDE Link with Microsoft Excel.' I tried it with the same document on another machine and it worked... Is there another way other than reinstalling Word and Excel to fix an issue like this. I already tried /unregserver and /regserver and also tried creating another document with a mail merge.

Thanks a lot in advance!!
 

Read other answers
RELEVANCY SCORE 68.8

Hi,
I'm completely new to vba and trying to modify a code I found here but failed.
While the original code exports cell data (1 row only) from excel into the form fields of a Word document, I tried to modify the code to work for multiple rows, one after the other.
My code works for the first row and generate a word file as supposed, but immediately stoped with an error message "Runtime Error -2147417848 (80010108), "The Object Invoked Has Disconnected from Its Clients"
Could anyone help me modify the code so that it works please? Thanks a lot!
I'm using 2003 excel and word.
P.S. I know this can be easily done by mail merge but I would like to use Word form for further process.

Code:
Option Explicit
'Requires reference to Microsoft Word (11.0) Object Library
Sub Copy_Cells_To_Word_Document()
Dim rng As Range
Dim row As Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Set rng = Range("A2:E4")
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Filename:="C:\Desktop\file.doc", ReadOnly:=False)
wdApp.Visible = True

For Each row In rng.Rows
Copy_Cell_To_Form_Field wdDoc, Range("D" & ActiveCell.row).Value, "Surname"
Copy_Cell_To_Form_Field wdDoc, Range("E" & ActiveCell.row).Value, "Forenames"
Copy_Cell_To_Form_Field wdDoc, Range("B" & ActiveCell.row).Value, "Ref"

wdDoc.SaveAs ("C:\... Read more

A:import excel data into word form fields

Read other 6 answers
RELEVANCY SCORE 68.8

I have two workbooks that I am using and I am trying to add one cell's data to another cell in another Excel workbook. It used to be as simple as beginning to "add" another cell's data(from another workbook you just migrate to the other workbook) into a different workbook. It isn't working like it used to in Excel 2003. Cant' speak of it for Excel 2007 as I never had it.

Any thoughts on why it won't work? Any way to make it work?

A:Excel 2010 - Workbook to Workbook Reference?

  
Quote: Originally Posted by Lewiedude


I have two workbooks that I am using and I am trying to add one cell's data to another cell in another Excel workbook. It used to be as simple as beginning to "add" another cell's data(from another workbook you just migrate to the other workbook) into a different workbook. It isn't working like it used to in Excel 2003. Cant' speak of it for Excel 2007 as I never had it.

Any thoughts on why it won't work? Any way to make it work?


Merging Data from Multiple Workbooks into a Summary Workbook in Excel

Looks like it's possible.

Also I would look at the DATA Tab under DATA TOOLS - CONSOLIDATE.

Read other 3 answers
RELEVANCY SCORE 67.6

Hello, I'm trying to import data from an online website that constantly updates its database. However, I can't do it with the Web Query from Excel 2013. After showing the Script Error display, I clicked continue instead of stopping it just to find out that I couldn't scroll down the Web Query page. Furthermore, I couldn't cancel so I had to use Task Manager to end the task of Excel 2013. I'm aware that it is using IE as a browser for the Web Query, maybe changing the browser would help? If it's not possible to do so, how can I fix this script error problem?

--------------------------------------------------------------------------------------------------------------------------
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 8.1 Single Language, 64 bit
Processor: Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 8130 Mb
Graphics Card: NVIDIA GeForce GTX 745, -1 Mb
Hard Drives: C: 304 GB (210 GB Free); D: 18 GB (2 GB Free); E: 606 GB (522 GB Free);
Motherboard: Hewlett-Packard, 2B2C
Antivirus: Microsoft Security Essentials, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 66.4

Hello, advicer
I do not understand why sometime I cannot copy a range from a workbook to another workbook in EXCEL (but I can if i paste the range in the same workbook). My PC is in the network of the company using WinNT 4.1. Please help me
Many thanks
Qan
 

A:Excel: Cannot Copy from Workbook to Workbook

Read other 7 answers
RELEVANCY SCORE 66

Hello-

While importing the essbase retrieved data from excel spreadsheet to access database using the folling Access VBA command, I get 65 K of blank rows into the table. This only happens for essbase retrieved data, other excel files works just fine. Is there a reason, and how can I prevent this 65 K blank rows? Please help. Thank you much.

VBA command to import excel data

DoCmd.TransferSpreadsheet acImport, nSpreadSheetType, "SPECTRUM_CALCULATION", _
cFileName, Me!ChkBox_FieldNames

Santosh
 

A:import essbase retrieved excel data to access table adds 65 K blank rows

essbase is probably using empty strings, which Access picks up as data. Instead of importing to a new table, set up your table with a primary key, one that corresponds to the Excel column most likely to have data in it every time. Set allow empty strings to no on that field as well. That should import only the "filled" records.
 

Read other 1 answers
RELEVANCY SCORE 64.8

Hi Guys,

I've an excel sheet which has some data and I want that excel to be automatically mailed every month end to few mail ID's. How to acheive this ?

-santhu
 

A:Excel should mail its data everymonth end automatically

Read other 11 answers
RELEVANCY SCORE 64.8

Hi all,

I have a monthly report that has a certain column (always column BF) that contains the numbers 1-5, either singly or in any combination thereof. I have to copy this into 5 columns (BQ through BU) and I want those columns titled Q2 - 1, Q2 - 2, Q2 - 3, Q2 - 4 and Q2 - 5.
I need all the 1s in Q2 - 1, all the 2s in Q2 - 2 and so on.
Since the number of items in each cell varies, (there is no 1, , 3,4, , with blanks for non-existing numbers, rather one cell may have 2 and the one below it may have 1,3,5) is there a macro or a formula that would quickly copy every item into the right column?
It isn't vital, I suppose, I can copy, sort and cut and paste, but I would like to save the time....

Thanks in advance
 

A:Solved: Excel 97 - want to automatically move data

Read other 9 answers
RELEVANCY SCORE 64.8

I have an Excel workbook with about 15 sheets in it. I would like to import each into its own table in Access.
The top row contains header,
The sheet name should be the table name,
All fields should be text,
And there should not be a primary key.
I've never made an Access macro - only Excel - so I'm a little lost. Thanks!
 

A:Solved: Access Import Enitre Workbook

Read other 7 answers
RELEVANCY SCORE 63.6

Afternoon everyone!

I am trying to get a little bit of code working such that when a user enters in some data in column A, it triggers code in VBA to paint a simple surround black border over the whole row, from column A to M. Here is what I have so far, but it doesn't seem to be working. I know I am missing something (if not a lot).
Code:

Dim i As String

If ActiveCell.Column = 1 Then
If ActiveCell.Value <> "" Then
Range(Cells(i, 1),Cells(i, 13)).Borders.LineStyle = Excel.xlLineStyle.xlContinuous
Else
End
Else
End

I appreciate any help! Thank you in advance!
 

A:Solved: VBA in Excel: Automatically paint borders on data entry

Hi Maxx, you shouldn't forget to mention the version of Excel you're using

Place this code in the sheet
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <= 13 Then
If Target.Value <> "" Then
Range("A" & Target.Row & ":M" & Target.Row).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Target.Offset(0, 1).Select
End If
End If
End Sub
 

Read other 2 answers
RELEVANCY SCORE 63.6

I have a spreadsheet that connects to a database.
When I open the spreadsheet, I got asked if I wanted to refresh the data. I did so, and clicked yes.
Some time later, after getting bored ticking 'yes' every time I opened the sheet I ticked the 'don't ask me again' button.
Now every time I open a sheet that pulls data from a remote data source, it automatically connects and updates the data - I no longer want this to happen!!!!! Any way I can switch this off as easily as I switched it on????

andy
 

A:how do i stop EXCEL automatically updating from a remote data source?

hilight all live data and right click and select Data Range properties. Remove the tick from Enable Background Refresh. then ok then Save.
 

Read other 2 answers
RELEVANCY SCORE 62.4

Hello my fellow databasers.

The Information:
I am currently working for a company that is tracking their part(s)
expenses per each customer job.

All customers' expenses are entered into the same 'primary' workbook so entering information is easier than going to each customer workbook. The column headings are as follows.

Customer Name - Date - Part Used - Cost - Invoice # - Additional Notes

There is 1 primary workbook and then each customer has their own workbook.

I have researched for a week now and am not savvy enough with Excel to know exactly what I should search for. I believe a macro is what I want though.
The question:
How, or what would I do, to have each workbook search for its relative 'Customer Name' and paste (auto populate) the information into the workbooks?

ex. I entered a part for John Doe within Primary Workbook. The second workbook sees John Doe, copies the row of all parts that have John Doe in the first column and populates it in the second workbook.

Also, would I need to create a separate Vlookup first to add a value to my customers so it wasn't text based to help the above equation work?
I know I could copy and paste each one, but sometimes we retroactively change values of costs, and for the amount of time I could save by creating ... a macro? or some formula would be well worth continued research. Any help is appreciated.

Application:

It's important for my employer to be able to see what parts were used ... Read more

A:Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

hi
A macro can do what you need but have you considered using filters on the primary workbook where you can select the customer name and only those records will be displayed? This will eliminate the need for multiple workbooks and make your life much simpler. An added benefit would be the ability to filter on other criteria like Date Or Part # so you can see across Customers what occurred on a specific date or which customers ordered a specific part.
Alternatively, this would be an ideal application for Access which would make data entry simpler and provide the information you need in the format you need.
 

Read other 2 answers
RELEVANCY SCORE 62

Good morning / Good afternoon
As a newbie I am making the first posting on your forum and I hope it is not a silly question.

I received a hard print of an excel spreadsheet with data in 9 columns (2 date formatted and 6 number formatted) of figures from my by bank and I wish 2 use them for further calculation. As I didn't get an electronic file from the bank I scanned the data sheet on the HP Scanner into text files ( rich text, plain text etc) and attempted to import the text into Excel; but I was unsuccessful. Have I attempted the impossible or did I do something wrong ?

If it is possible to do so how should I proceed ?
With thanks, anothernub
 

A:How 2 import text data into Excel from scanned text file ?

Read other 12 answers
RELEVANCY SCORE 61.2

Hi all,
I have searched for a solution to this but as yet I haven't found the right thing.
I have a work book that was made with the help of Hans on here (A sheet to track training) that automatically sends emails out and updates the sheet which is working great.
What I am now trying to do is to have the sheet auto update depending on data in another excel file.

I get sent the data each week and I would like my tracker to automatically read the other file and insert any new data, is this possible without ending up with lots of duplicated rows or incorrect data?

I will upload a sample of the tracker that was made on here with Hans later this afternoon and also a sample of the file I receive each week to see if its possible, I have been playing around with it but I cannot seem to understand how to have it read the other file and update the relevant fields.
An important part to this is the cells on the tracker would need to keep their conditional formatting and any formulas relating to the cell as if I had typed the new data in myself.

I hope it makes at least a little sense, please do let me know if clarification is needed.

Thanks for your time once again.
 

A:populating from one workbook in to another automatically

Read other 9 answers
RELEVANCY SCORE 60.8

Hello,

I've been working on a certain workbook and everytime a try to save it an error occurs:

Microsoft Excel for Windows has encountered a problem and needs to close. We are sorry for the inconvenience.

AppName: excel.exe AppVer: 9.0.0.2719 ModName: excel.exe
ModVer: 9.0.0.2719 Offset: 002aa2b3

HELP !!!

Thanks

Mark R

A:Excel 2000 .. When saving a certain workbook excel crashes

Hi, try running a repair from control panel>click on MS office and run change or repair if I remmber correctly.

Read other 1 answers
RELEVANCY SCORE 59.6

I have a workbook located on \\server\servershare\type2015
file name: TYPE.xlsx

how do i write the path of the table name in my query.

Any suggestions?
Thanks.
 

Read other answers
RELEVANCY SCORE 57.6

Client has SharePoint 2010 project server.
When the user clicks on a Excel file, the file open in the browser and when the user click on "open in Excel" we receive the below message 
"To open this workbook, your computer must have a version of Microsoft Excel installed and your Web browser must support opening files directly from Excel in the browser."
Below is the browser version 


However when the same is tried/accessed through a browser deployed on a dev machine, it works normally. Below is the version of the IE on the DEV server

What is causing this to happen?
Is there any tool to check the difference between the IE settings on my Desktop and the dev box.

Satyam....

Read other answers
RELEVANCY SCORE 57.6

Can you have wildcards in a filename? i.e

='U:\CurrentReports\[Richmond-Report-022307.xls]Stats'!$C$2+'U:\CurrentReports\[Tampa-Report-022307.xls]Stats'!$C$2

The part that needs to be different is the part after -. This is the date of the report. That way it always gets the most current data. Can this be done in the cell itself or are we talking VBA codeing?
 

A:Retrive Data from one Workbook into another

Read other 9 answers
RELEVANCY SCORE 56.8

I have different database in several worksheets. I want to use vlookup to fill in the data from different worksheets rather than combined all into one worksheet. Can it be done? Thanks.
 

A:How to use vlookup for data from several worksheets within the workbook?

Welcome to the board.

http://www.ozgrid.com/VBA/VlookupAllSheets.htm (not tested).
 

Read other 1 answers
RELEVANCY SCORE 56.8

My O/S is Windows Vista and I am running M/S Office 2000 Professional. In the past when using Windows XP I have been able to change which workbook opens when I open Excel. Now with Vista, I have designated 2 different workbooks to open when Excel begins however I cannot delete them form the Excel XLS folder. When I delete them, the screen shows that they are gone however when I strat Excel afterwards, they are back! How can I dlete them and install a new workbook. The standard process shown in the Excel help files is not working. Thank You.
 

A:Excel Startup Workbook

Closing duplicate...........replies here: http://forums.techguy.org/business-applications/672809-revise-excel-startup.html
 

Read other 1 answers
RELEVANCY SCORE 56.8

Howdy, kinda new here. Anyway, I've got a doozy I can't seem to figure out. We just installed dual-monitors and now Excel is coming up saying "Not enough resources to display completely." It does this even when Excel is just started, with the blank, new workbook. There doesn't seem to be any screen real-estate issues since there's now double the screenspace. Any thoughts or ideas to try would be greatly appreciated.

-Ted
 

A:Not enough resources for Excel, even with a new workbook.

Read other 13 answers
RELEVANCY SCORE 56.8

Tech Support Guy,

I have a question regarding linking two Excel (I am using Office 2003) workbooks and varying worksheets.
Here's what I want to do, using a macro, create a link to a cell in workbook A/worksheet X to workbook B/worksheet X (where X is variable).
Now creating a link is easy, except I want the "link" itself to appear in workbook A.
I tried the following:
Workbooks("Workbook A.xls").Worksheets(Worksheet A).Cells(6, 4).Value _
= Workbooks("Workbook B.xls").Worksheets(Worksheet A).Cells(57, 3).Value
However, Excel places the value instead of the link in the cell. I want both, meaning, you see the value in the cell, but when you highlight the cell, you see the link to the 2nd workbook.

I have also tried the the following:
Workbooks("Workbook A.xls").Worksheets(Worksheet A).Cells(6, 4).Value _
= "='[Workbook B.xls] Worksheet A '!R57C3"
This gets me what I need except as I mentioned, the worksheets vary and I have not been able to figure out how to do use a variable worksheet name in this example.
I appreciate your help in solving this problem
Take care,
RJ
 

A:Excel Workbook Question

See if using the indirect function is the solution?
 

Read other 1 answers
RELEVANCY SCORE 56.8

Hi,

I am working on an Excel 2002 report. I need to be able to create a copy of the workbook, strip it down a bit (for uploading purposes), and let the user save it as he/she pleases.

Is it possible to make an active copy of a workbook without saving it?
Excel VBA help is not being very "helpful" on this matter.

Thank you,
ABentsionov
 

A:Copy Excel Workbook

Hello, welcome to the board!

It is always best if you work from a saved file. This is just generally a good idea. Is there a reason you do not want to save the workbook? You need to save to do what you want. There is a way, but it is more convuluted than just saving your file.
 

Read other 3 answers
RELEVANCY SCORE 56.8

When I attempted to save an Excel workbook, I couldn't because the file name had changed to a number. What should I do?
 

A:Can't Save Excel Workbook

Read other 7 answers
RELEVANCY SCORE 56.8

I write a few macros for other users, and right now I just request that they rename the workbooks each month (or however often they run it) to the same names and then offer a reminder at kick-off. Something like:
Code:
If MsgBox("Are the workbooks saved as 'Workbook_1.xlsx' and 'Workbook_2.xlsx', and are they both open?", vbYesNo) = vbNo Then
End
End If
Windows("Workbook_1.xlsx").Activate
Range("a3").Select
Etc, Etc
Otherwise, I don't know how to make changes or run actions based on workbooks whose names might change. But is there a better way? I've had some people - those least familiar with automation - forget to rename, ignore the message, and then call with problems. Perhaps there's a way, instead, that asks them to name as variables whatever the sheets are called, though this might require some hand-holding, such as a list of what workbooks are open? I don't know. I'm open to suggestions - just putting it out there.

Thanks!

Windows XP
Excel 2007
 

A:Excel VBA: Prompt for workbook?

Read other 10 answers
RELEVANCY SCORE 56.8

Hi,

I am using Office 2013. I have a workbook which has to be shared. I want to protect it in a way that none of the options can be changes (like I have enabled 'track changes'. 'highlisght changes from users' etc). I do not want that someone turn off the 'track changes' option or as a matter of fact any other option.

They, however, should be able to edit the workbook. Is it possible?

I tried searching but could only see an option to protect workbook structure but noes not protect options.

-regards
 

Read other answers
RELEVANCY SCORE 56.8

Hello guys.
Please guide me through building my own tool in Excel.
I need to have workbook WK 1 with macro to modify another workbook WK2 which already have macros in it.
That is my plan : WK1 should have several command buttons first is to find person by ID from WK 2 , copy entire row with persons details to WK1 list 1, another button same but copy entire row to WK1 list 2, than when both lists created, I must have third command button which will : for each person in List1 WK1 will change value in cell 1 in WK2 from none to 1 and cell 10 enters =today date, for each person in List2 WK1 will delete value in first cell of the row in WK2.

I am keep loosing the track of what would be most simple way to do those steps, WK 2 file name is changing every day with date in beginning of the name of file,
 

A:Need help Excel 2 modify other workbook

Read other 13 answers
RELEVANCY SCORE 56.8

I have an Excel (2010) template from which I need to create separate workbooks for our range of ocean-going vessels.

Requirement 1
Upon loading the template, I need to prompt the user to enter the name of the vessel.

Requirement 2
The name must be transferred to a particular cell, probably A1, in proper case.

Requirement 3
I then wish to save the workbook using the vessel’s name, in capitals, followed by a space, a hyphen, another space and then the rest of the filename – based on the name of the template.

Example 1: name of template Record of voyage.

Example 2: name of workbook OCEAN TRAVELLER - Record of voyage.

Any thoughts?

 

Read other answers
RELEVANCY SCORE 56.8

I created an Excel workbook to monitor my investments. One of the work sheets contains a long column of currency values from an annuity investment with each row showing daily values.

Presently, I manually copy the daily value of that investment and paste it into worksheet # 2 that contains all of my investments. Worksheet # 2 daily refreshes, updates and totals all investments.

My question is this: is there a formula that would automatically copy and paste the daily annuity values from worksheet # 1 into worksheet # 2 without having to manually copy and paste? If so, could you please guide me through the steps to accomplish this?

Many thanks for your prompt attention and cooperation.
Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Quad CPU Q8200 @ 2.33GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 4
RAM: 4028 Mb
Graphics Card: NVIDIA GeForce 9800 GT, 1024 Mb
Hard Drives: C: Total - 476837 MB, Free - 414208 MB; D: Total - 114439 MB, Free - 21161 MB;
Motherboard: Intel Corporation, DG43GT
Antivirus: avast! Antivirus, Updated and Enabled

<edited by moderator (etaf) to reduce font size - not required default forum font size is adequate >
 

A:Excel investment workbook

Read other 9 answers