Over 1 million tech questions and answers.

Solved: Excel macro to extract Word form data into Excel

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

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
____________________________

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel macro to extract Word form data into Excel

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: Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 116.8

Hey all,

I have come to you in a fit of desperation. My USB drive was stolen recently and my master Excel data file was lost, which contained all my compiled data from my most recent lab experiment.

Anyway, I have charts of all the important stuff in a word document. The data is there, I can hover on each data point and see the values, and the axes and chart are all accessible for editing.

My question is, how the heck do I extract that data back out of the chart into a table or Excel spreadsheet? There MUST be a way that is better than copying down each data point one at a time

Your help is greatly appreciated.
 

A:Excel chart in Word 2010: extract chart values? Lost Excel data file!!!! Help.

Hi welcome to the fourm.
I don't know it reverse engineering is possible.
Something like convert chart to table?

One thing you could try is copy the chart back to a new Excel file and see if more is possible there than in Word?

Like I said never needed this. Have you googled for something like Excel data reverse enginieering?
Maybe something come up.
 

Read other 1 answers
RELEVANCY SCORE 115.2

Running WinXP and Office 2003

Hello. This is similar to http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html

I tried using and tweaking that example but am having a hard time because there are several different things.

I will be having hundreds of word documents. The first page on each doc is just a form that a user fills out on the computer. I would like some help with the macro that would run and process a folder of these docs.

I would also like to have 3 sheets in the one excel file. The doc file will be filled out generally by 3 departments. Is there a way to have a drop menu in the doc to indicate which dept and also have this transfer to their respective sheets in the excel file? The rest of the doc fields should be the same. Also I need the hyperlink to the file in their respective cell.

Sorry I am new to code in excel and am starting to learn its power. Thanks for your help!
 

A:Solved: Exporting Word Form Data to Excel Spreadsheet

Read other 16 answers
RELEVANCY SCORE 115.2

Hello I found an answer to this question but it was specific to his form. I have several Excel Logs (Tables) that contain information that is also entered into a separate form. Some forms are in excel and some are in word. I would like to hit a button that transfers a row of data to the form so i only have t enter data once. The forms will have additional information that will be entered. The forms are saved as a specific file type and are also printed so the answer that i found on this site may work but it puts the data into a different spread sheet in the same workbook and i need to send the data to a new workbook and in one case to a word doc. Can someone help me out. I have attached a couple examples of what i currently do.

Thanks for the help
 

A:Solved: Populate Excel Form and Word Form from Excel Table

Read other 16 answers
RELEVANCY SCORE 114.4

HI

I'm new here. I have inherited in my job more than 400 forms in a word format, same fields and same location, and I would like to extract these data into excel to then be able to analyse the data.
Each Ms-word file template contains one record with several fields. Not all the fields are or will have information.
Is it possible to do this? Do I need to VBA or is there a clever trick for this.

I have attached the template for reference.

Can anyone help me please.

Many thanks
 

Read other answers
RELEVANCY SCORE 114.4

Hi all,

I'm looking to create a macro that will extract specific text from Word and place it into specific cells in an Excel template. Also, if the text in word is for example underlined, bold, italicized or has line breaks, then it would need to add the HTML formatting to that word. (ex. <u>word</u>.)

I'm stuck on how are we going to identify how to pull the text and then put it in the correct cell.

I have attached some documents. I need column G on the sto22 to be populated with specific text from the sto22_hungarian doc. The STO22 English Text IDs doc can be used to identify what cell lines up to what piece of text, based upon the text id and English translation in that row.

Pretty new to this so far...

Read other answers
RELEVANCY SCORE 113.2

Hello all,

I have been presented with a project that involves Forms that I am not able
to resolve.

Basically I have been asked to create a document template where other team
members can fill out a form that asks a series of questions, then to have
only the completed form results export to a new file.

I am aware that Word can export the form results to a text file, but when I
do this all of the questions that were on the original form are exported to
the new file.

What we are looking to achieve in the end:
If the end user is faced with 20 questions, but only 12 of the questions
pertain to the end users project, only the 12 completed form fields will
export to a new file.

I know that this should be possible with using a macro button to export the data, but that is where I need the help.

I tried searching for this exact situation, but I did not find any matches, hence the new thread.

Thank you in advance,

Bill
 

A:Macro help needed to export Word form results to a new file... Word/Excel

Read other 11 answers
RELEVANCY SCORE 108.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 107.6

Hi Everyone,

Thank you for all your wonderful work on these boards. Your advice has helped me time and again, although this is the first time I'm posting myself.

Here is my trouble.

I adapted script written by Rolin_Again written for a poster named DougS when he was attempting to move data from a Word Form to Excel. I too need to have my Form in Word as it's a mass email and most of if not all of the recipients of the form will not have Access (oh how I wish they had Access). I have written my Form and attached it for you and saved an Excel Workbook called "Registration". The sheet is also called Registration. When I first ran the Macro I ran into the same trouble the poster did here with the Form opening when I used the macro, moving to the "processed" folder but no data transfer into Excel. I tried the fixes offered in that post but not successfully it seems. I am now having a new error and I have highlighted the line that the debugger points out as a problem (Run-time error '9'': Script out of range).

Any help anyone could offer would be much appreciated. I have never written code so this is all very foreign to me.

Thank you in advance. I have seen how helpful everyone is on these boards and I'm very grateful for the help.

Best,

Heather
MACRO:

Dim vField As FormField
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Doc... Read more

A:Capturing Word Form data into Excel... Again, Again

Read other 14 answers
RELEVANCY SCORE 107.6

I am trying to make an database of members for a racing organazation.

Each member has a word document which contains the membership form filled out with Word's Form feature. I know you can do Options > Save > Save Form data only, then save for each file, but i'd like to automate the process and save only form data for all of the documents at once.

Secondly, I'd like to import them into an excel spreadsheet. I've used the data import feature, but once again, the process has to be repeated for every single file. I cannot select more than one. Again, I'd like to autmate it. How?

Better yet would be a method to take all the .doc file's form data straight into excel without going to the middle .txt file.

Thanks for your help,
Doug
 

A:Exporting Word Form Data to Excel

Read other 14 answers
RELEVANCY SCORE 107.6

Rollin wrote this code for someone who wanted to dump Word Form Data into Excel. It was quite a while ago so I guess it was for Office 2003 or some such. I have Office 2010 and I want to do the same thing. The code seems to run OK, as it opens the Word files and dumps them in the Processed directory but nothing turns up in Excel. I have tried with xlsx/docx and xls/doc files. I've been at this all day and I can't suss it. Please, how do I get it to work?

Dim vField As FormField
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer

Sub AddFormFields()

vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject

Set fsDir = fso.GetFolder _
("C:\Documents and Settings\Colin\Desktop\IFR_Applications\UnProcessed\")

Set wdApp = New Word.Application
wdApp.Visible = True

For Each fsFile In fsDir.Files

wdApp.Documents.Open (fsFile)

Set myDoc = wdApp.ActiveDocument

For Each vField In wdApp.Documents(myDoc).FormFields

vField.Select

vValue = vField.Result

Workbooks("Applications.xls").Activate
Cells(vLastRow, vColumn).Select

If vField.Type = 71 Then

Select Case vField.Name

Case "Check1"
vColumn = vColumn - 1
If vField.Result = "1" Then
ActiveCell.Value = "YES"
End If

Case "Check2"
If vField.Result = ... Read more

A:Capturing Word Form data into Excel (Again)

Read other 14 answers
RELEVANCY SCORE 107.2

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 106.4

Hi there,
I'm trying to export data from Word 2007 form fields into an Excel spreadsheet. I was hoping to have the spreadsheet contain data on a cumulative basis for the year or quarter at a minimum. Not sure how the code runs and if this is even possible. Does it scan a folder for all pertinent info to populate the spreadsheet?

Currently we are writing about ten of these docs a day and the data entry is becoming burdensome.
I have attached an example of the spreadsheet for reference.

I can provide specific examples of file paths or folder names if that becomes necessary.

Any assistance is greatly appreciated!

Thanks!
Cameron
 

A:Exporting Word form field data to Excel

Here's the word doc with form fields associated with the spreadsheet.

thanks!
Cameron
 

Read other 2 answers
RELEVANCY SCORE 106.4

I am trying to make a database based on survey data collected in Microsoft Office Forms.

Each subject has submitted a word document which contains responses to survey questions filled out with Word's Form feature. I would like to automatically populate an Excel spreadsheet with the form fields from a large group (25-50 documents per batch).

I have seen questions similar to this raised in other forum posts, but I seem to be running into problems with the suggested macros. This might be because I am working on a Mac (the only computer at our company) in Microsoft Office 2011. Any suggestions?

Thanks for your help,
Sasha
 

A:Exporting Microsoft Word Form Data to Excel

Hi Sasha
If you are wanting to use VBA to copy / move between Word and Excel you will almost certainly need appropriate boxes ticked under references in the VBA editor for Microsoft Excel XX Object Library.
I've no idea whether the Mac version is set up the same and would imagine you would have a bit of work to do to translate VBA written for windows into a mac variant.
What you might do is consider providing some dummy data and files and seeing what we can do to help.
 

Read other 1 answers
RELEVANCY SCORE 106.4

Hi,

I have created a protected Word form, which comprises of text fields, drop down fields and tick boxes. I have an Excel spreadsheet with columns corresponding to most of the labels in my Word document.

Is there any way in which I can selectively export data from the Word form to the spreadsheet such that selected form fields data is exported to selected columns as row entries?

Hope my request makes sense.

Leegirl
 

Read other answers
RELEVANCY SCORE 106.4

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 106.4

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 106.4

Hi Everyone!

I have a form that I created in Word 2003 (see attached). There are a few things that I’d like to accomplish with this form.

1) once the form is completed by the user, I want to be able to export the entered data into an excel spreadsheet (via macro button – submit/export - and VBA for the actual transfer of data?)
2) for 3 of the sections with radio boxes (employment type, rate your experience, and the 1st line of availability (1 shift/wk, 2shift/wk…)) I would like only one selection to be made. So the user can only select one of the options, and not multiple. Actually the Teaching Experience one…I need it so they can select whichever grades they teach, but only one selection of ‘years taught’. They can select any/all the MPMxx but it’s the ‘years’ that is important…

I have a very basic understanding of VBA but I work with excel all the time so I’m relatively comfortable with some things and willing to learn/try new things…

Can someone please point me in the right direction as to how I can accomplish this?

What I envision is the form being completed then sent back to me. I then hit a button embedded in the form that will then export the data to my excel spreadsheet where I can sort, etc…

Any help would be much, much appreciated as I’ve tasked myself with completing this…when maybe perhaps I should have tried to figure out how complicated this might be first…

Thanks for reading!!!
 

A:[VBA] Exporting Word 2003 Form Data to Excel

Files
http://www.mediafire.com/?xxexol8mexwi1ma

Okie, my objective here is to transfer the data from a word .doc into an excel spreadsheet. The form is all bookmarked with proper tags. I have started the VBA code, but I’m at a loss as to why some things aren’t working…


Sub Report1()
Dim path As String
Dim wdApp As Word.Application
Dim wdDoc As String
Dim curDoc As Word.Document
Set wdApp = CreateObject("Word.application")
wdApp.Visible = True
path = "C:\Users\xxxxxx\Documents\APPLICATION FORM\FINAL COPIES"
'Get first document in directory
wdDoc = Dir(path & "\*.doc")
'Loop until we don't have anymore documents in the directory
Do While wdDoc <> ""
'Open the document
Set curDoc = wdApp.Documents.Open(path & "\" & wdDoc)
Range("A1").Select
Selection.End(xlDown).Select

ActiveCell.Offset(0, 1).Value = curDoc.FormFields("DATE").Result
ActiveCell.Offset(0, 2).Value = curDoc.FormFields("FNAME").Result
ActiveCell.Offset(0, 3).Value = curDoc.FormFields("LNAME").Result
ActiveCell.Offset(0, 4).Value = curDoc.FormFields("EMAIL").Result
ActiveCell.Offset(0, 5).Value = curDoc.FormFields("OCT").Result
With ActiveCell.Offset(0, 6).Value
If FieldName = True Then
ActiveCell.Value = "PERMFT"
ElseIf FieldName1 = True Then
ActiveCell.Value = "LTOS"
ElseIf FieldName2 = True Then
ActiveCell.Value = "RET"
ElseIf Fie... Read more

Read other 2 answers
RELEVANCY SCORE 106

Good Morning guys,

This is my first post on Tech Support Forums!

I am not to sure how much help I can be to people here other than building gaming Rigs, so I will keep one eye on that forum to see if I can add value here.

Now, on to my question!

I have assembled an Excel spreadsheet with hundreds of lines of date. (Business Name, Address, Contact Number, Email, Website). We have a CRM system which does not allow me to enter new clients myself, so i have to complete a CRM Request form for each potential client on this list (over 600) The CRM request form is a Word Document with sections for each part of the data on excell.

The problem is, I am too lazy to copy and past all of the lines of data into individual CRM Request Forms (Word Documents)

So in short... correct me if I am wrong. I think I need to create some form of macro to do this for me. (copy data from excell, open up the word document, paste the data in the specific places, then save it, then repeat for the next line of data)

It would be greatly appreciated if you could point me in the right direction on this one people.

Kind Regards,
Anthony
 

A:Need Help: Excel data to individual word documents, Macro Maybe??

Read other 7 answers
RELEVANCY SCORE 106

Hi guys i'm hoping you can help me i'm a complete noob when it comes to macro's
 

A:How do i create a macro in Word that opens the data in Excel?

Read other 12 answers
RELEVANCY SCORE 106

I'd like to create a macro which creates a new MS Word document in the same directory as the excel book which copies specific data from an excel worksheet into the new document. The items contained within the cells should appear as a bulleted list on the word document, not a table.

Is this possible?
 

A:Macro to Export Excel Data to New Word Documet

I'm not a Word macro person at all..But I'm thinking (quick and dirty) you could do this with mail merge. Don't use the address part just 'insert merged' fields...Your 'cells' would have to be in the same row in excel.

There are also excel to word conversion programs(google). I have never used one. If your data was in Access a 'Report' would suit your needs. Also a script that reads the excel file and writes to file and saves as .doc might do this also.

Going to word 'for the sake of being in Word' seems counter productive but management wants what management wants.
 

Read other 1 answers
RELEVANCY SCORE 105.2

What I have got is a word document and an excel document.

The excel document is used to enable a userform which a person can fill out.

The word document is used for the details of the user form in excel to be transferred to the word document.

I have created the forms and the word template and i am looking for a way the following can be done when the "SAVE" button is clicked on the user form in excel.

1) Have the details from the user form transferred into the word document at the top where the corresponding headers are.

2) The word document file name automatically created(using the issue title in the user form) and inserted into the excel sheet under issues for it to be reopen and viewed in Microsoft word.

I know it would be simpler to have all this in one form but the idea is to get someone to fill out the issue only then someone of higher authority can update the issue's progress but still allowing the issue creator( or any one else) the access to re-open it and see the updates in a read only format in word.

complicated i know but that's how its got to be.

attached are the word doc and the excel doc

(See attached file: Issues Log new.doc)(See attached file: new sample issues log.xls)

Thanks
 

Read other answers
RELEVANCY SCORE 103.6

I have a table with rows and columns. See below.

Number 1 2 3 4 5
1 1 1 1 1 1
2 2 4 8 16 32
3 3 9 27 81 243
4 4 16 64 256 1024
5 5 25 125 625 3125
Input row say x
Input coloumn y

Answer =

If I input row as 5 and coumn as 2 in the answer cell the result should appear as 25. The answer cell should not contain formula.

What is the solution for the above. Use of Vlookup, Index and Match etc.
 

A:Solved: Excel 2003 - Data extract

Read other 6 answers
RELEVANCY SCORE 102

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 101.6

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 100

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 99.6

Hello,

I have two problems:

1.
I have a lot of scanning data, all date and time stamped. All the scans show different status of shipped packages (all shipments have unique ID#, but there are multiple scans on route for all of them to track the packages). I want to find all the packages, for which there was "misdirected" scan, then for all of them, show the scan before. So, the end result would be all the scans that show for each package where it was misdirected (the terminal# of the scan before the misdirect scan).

2.
I want to automate this: run 5 queries every two hours between 8am and 10pm, copy and paste all results into Excel to specified areas (e.g. first query results to A1, second to A5000, third to A10000, etc. or onto different tabs), save as .csv to a specific folder, then send it as attachment to a given e-mail address. Even partial solution can be good.
I only know macros (somewhat), not as much VBA, so I tried that with the TransferSpreadsheet command, but can't get it work.

Any help is greatly appreciated.
 

A:Solved: ACCESS: Show second latest data and export data to excel from macro

Read other 14 answers
RELEVANCY SCORE 99.6

When i export my inventory from my DB into an excel file, my trailer brands are listed as they are in my DB (of course). But when i bulk upload them with XML feed to trailer inventory sites (HTW.com & HTT.com for our example here). My inventory is listed on their site, but it's not searchable because the brand search is a drop down menu on these sites.

Say i goto HTW.com and do a search for 'Blue Ribbon' trailers none of my blue ribbon trailers show up because all of mine are listed as 'BLUE RIBBON TRAILERS, LTD' and the site is searching for 'Blue Ribbon' and although they are the same trailer brand HTW.com does not see them.

What i'm looking for is maybe a Macro or even formula that once i tell it what site i'm working on, will go through my list and replace all my spellings with the proper ones for that site...

Attached is an XLS file with 3 columns, the first is my list as is from my DB, the other 2 columns are the proper spelling for each corresponding site...

Excel's find and replace feature is a VERY crude fix to this, but since it's so time consuming it's NOT a viable solution.

Any takers??

Slurpee? Zack? Anyone??
 

A:Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

cheers
 

Read other 3 answers
RELEVANCY SCORE 97.2

Running WinXP and Office 2003

Hello. My question is same as http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html

Thanks to Rollin_Again's script, I have managed to export the Word form data perfectly into the Excel. However, as I close the Excel, reopens it and re-run the macro, the new data will somehow overwrite the last row of the previous batch of data.

Example:
Batch 1: 9 cases successfully exported from Word to Excel Row #2-10
Batch 2: 5 cases successfully exported from Word to Excel Row #10-14 (Row #10's previous data is overwritten by Batch 2's data...)

Have I missed anything? Can anyone help please? Thanks a lot!
 

A:Solved: Export Word Form to Excel

Read other 11 answers
RELEVANCY SCORE 96.4

Hi,

I need a help with a macro. I have two separate sheets that I need to combine together. Sheet 1 contains combinations of 2 PROCESSES, e.g. 1-2 and 1-3 (columns B and C) and sheet two contains additional data (PRGRAMS) for each of the process (each program on separate row - the process number linked to sheet 1 is in column A). So for process 1 there are 5 programs rows, for process 2 there are 3 rows and for 3 there are 8 rows. I would like to create a third sheet, that combines PROGRAMS (from Sheet 2) based of given combinations of PROCESSES from sheet 1 and attaches LHS (for the left side of the rule and RHS for the right side of the rule). I attached a shortened example. I need this for 600 combinations.

There could be a way in inserting number of rows into sheet 1 e.g. for the combination 1-2 (there would be the number of rows added that equales the count of "1" + count of "2" programs from Sheet 2 and the programs would be pasted).

I hope its not too complicated.

Thanks a lot for your help!

Marketa
 

A:Solved: Macro for combining data from two excel sheets

Read other 6 answers
RELEVANCY SCORE 96.4

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?
 

A:Solved: Excel Macro Copy Data From Columns

Read other 8 answers
RELEVANCY SCORE 96.4

I have a list of branches. We have a closed branch that I need to have inserted onto this report. There are times that the closed branch shows up becuase they had warranty work to do, but most times it does not show up. This is branch 046.

How do I write a macro to check for the branch and if not there add it in?

To add more to this - each branch should show up in 2 different rows in the same report. So I really need to do this for both Branch 046 and for Net Sales - Branch 046

Example
Branch 045
Branch 046
Branch 047
Branch 049

Net Sales - Branch 041
Net Sales - Branch 045
Net Sales - Branch 046
Net Sales - Branch 047
 

A:Solved: Excel Macro to insert row if data does not exist

Read other 12 answers
RELEVANCY SCORE 96.4

Hi,

I have a worksheet containing various data including an expiry date, this is created by adding 364 to the start date and then then using an auto fill to fill in all other rows (over 50,000).

I would like to make a macro to do this alongside a number of actions but when making the macro I am specifying only a certain range to fill (A1:A50000). The problem I have is this list is consistently growing and so this range would then not include any new records added. Is there a way that I can make it so it auto fills from A1 to the last present row?

Thanks for your help

Dan
 

A:Solved: Auto Fill Data in Excel Macro

Read other 8 answers
RELEVANCY SCORE 96.4

I'm in a bit of a pickle.

I have an excel document with a summary page ("Tracker"). This worksheet has a table in it with a whole heap of customer information, and at the end there are columns for revenue summaries.

Every month I run a report that I add in a new worksheet which is called "[Relevant Month] Revenue Report", and then I do a vlookup to transfer the revenue and product information in columns "C" and "D" of the report into the relevant month columns in the "Tracker". This has been working well for me, but i'll be handing this role over soon so I was wondering if there was a macro that I could use that would do this vlookup automatically for the relevant month?

The information in the reports is not in any particular order so i can't do a simple copy/paste into my "Tracker" summary.

Any help would be greatly appreciated!!
 

A:Solved: Excel Macro to transfer data from several worksheets into one

Read other 8 answers
RELEVANCY SCORE 96

Hi all,
i need to design an excel file for my company invoice which can be used as an input data form.
Let me explain:
1. the form should have a dropdown list in the part description column.
so that one can select the part in the first column.
2. according to the part description,appropriate rate should appear in the rate column.
3. and in the asse.value column the product of the qty and rate should appear.
4. the required part names and rates are there in the sheet1 of the attached file.

i hope i have cleared my requirements.
sample file is attached herewith.

thnks all...!!
 

A:Solved: design excel data input form for invoice

Read other 6 answers
RELEVANCY SCORE 96

Hi All,

Please find an attached .xls file.

I want to modify my data through Userform.

Can anyone help me in this regard?

Thanks
 

A:Solved: Excel UserForm - Modify Data through User Form

Hi All

I got it. I asked another expert in Microsoft Excel and he solved my problem within a day.

Here is the file.
 

Read other 1 answers
RELEVANCY SCORE 95.2

I have a cell that contains:
-Phone number
-Address
-Description

And there is no separator between this data. I'm hoping to think of a way to split up this data, but don't know if it's possible.

Examples:

Code:
(518) 352-7311PO Box 99, Blue Mountain Lake, NY
Code:
(800) 705-0059 / (518) 668-57552176 State Rt 9, PO Box 272, Lake George NYComprehensive information including: lodging, restaurants, shopping, attractions, special events, online coupons & special promotions.
Code:
(907) 474-8555
Note that this is not specific to the US, so the phone formats change...

Think it's possible to do something in Excel to split up this data?
 

A:Excel: Extract data

Read other 16 answers
RELEVANCY SCORE 95.2

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.
 

A:Solved: Excel Macro to Move Data in rows to columns

Read other 9 answers
RELEVANCY SCORE 95.2

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 95.2

Hi,
I have an employee list excel file (vacation) including the details about empno, emp name, last vacation, next vacation, etc in sheet 1.
Now, I would like to write a macro where I will enter the name of the month in a dailog box and the macro should display the list as in sheet 2 of the attached file.

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

A:Solved: Excel Macro to display data based on month

I've made a quick macro that does as you requested. It is not "smart" at all so if you change where the data is located (by adding a column for example) it will no longer work. It will handle more rows of data no problem. See the attached.
 

Read other 2 answers
RELEVANCY SCORE 95.2

Hi everyone,

Let me just start by saying this forum is awesome. Nothing like getting a new perspective to solving a problem!

Here's the scenario:
I have 3 sheets: Input Sheet, Output Sheet, Translation Table

Both the Input Sheet and Output Sheet has columns of data with a header row in row 1. I need to move entire columns of data from the Input Sheet to the Output Sheet based on the header row.

This is where the Translation Table comes into play. I need to move the columns of data based on what is defined in the Translation Table.

For example, all data in column "Column A" of the Input Sheet should be moved to column "Albert Column" of the Output Sheet.

A sample file is attached to better illustrate.

Thanks in advance for the help!

Simon
 

A:Solved: Excel Macro - Cut/Paste Data via Translation Table

Read other 6 answers
RELEVANCY SCORE 95.2

Hello,
I'm finally getting near to the end of my mission to get my trading website online but I have one nutty task left to crack with my data, and I could do with a bit of help please.

I have about 6000 products (graphic novels - I run a comic shop) and I have painstakingly put together my related product info in a spreadsheet laid out as in the 'current layout' below. For most of the products there are 4 related products (0,1,2,3) but for some there are only 2 (0,1) or 3 (0,1,2). The 0,1,2,3 simply pertains to the order from left to right the related products are shown on the site.

What I need to do is get them into the 'required layout' shown below so I can just cut and paste them into a specific Access table that already exists in the online trading software.

However, I'm struggling with this as my Excel skills don't really go in macros and loops and whatnot. (I'm pretty chuffed with myself when I use something like concatenate!)

I get the impression this should be reasonably straightforward though, which is very frustrating. Can anyone recommend an available macro or bit of code that does this sort of thing? Or am I just missing some very obvious functionality in excel?

many thanks in advance,
Jonathan
CURRENT LAYOUT (this is meant to be 5 columns - Product Reference, 0, 1, 2, 3 but it doesn't want to tab nicely)

Product Reference 0 1 2 3
9780867196818 9781593073558 9781593075330 9781593077778 9781560979494
978156... Read more

A:Solved: Is there an available macro to do the simple rejig of my excel data I require

Read other 6 answers
RELEVANCY SCORE 95.2

Hi
I am trying to export data from word or excel form into a spreadsheet. I have tried the macro at http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html and it worked for all text fields but not the drop down list options that I have in my form. Can anyone please help/

I have never done this before, hence the struggle. Any help would be much appreciated.

I attach the word form and the excel form which also includes the excel spreadsheet that will collate the data for analysis.

I would prefer the form in word and have it set up as that indicated in the forum thread above but picking up the data from the drop down lists as well.

Thanks
LAD786
 

A:Export data from word or excel into Excel spreadsheet

Hi there, welcome to the forum.
It seems this post you added on August 12th hasn't given you much answers.
You mention the two files, OK, But what I don't understand
I am trying to export data from word or excel form into a spreadsheet. ...
Click to expand...

You probably forgot to attach the macro's with it, that is if you did anything with the link you mentioned.
If you could attach the files with the macros, I could take a look and see what I can do for you.

Another thinng From Word or Excel.

Which one is it going to be?
 

Read other 1 answers
RELEVANCY SCORE 94.8

Hi,
I have posted this question with Microsoft Technet but no answer yet.
Based upon the following artikel:
http://www.microsoft.com/technet/security/bulletin/MS01-050.mspx

Because of a question in the Business Applications forum there was a vbs script to run open an excel workbook and execute a macro.
What surprised me is that it absolutely disregards the security settings and in no way asks if you wish to allow or disable macro's.
I tried if in Office 2003 (not yet with 2007) and any level I set security is works, no prompt.
The macro is unsigned and I tried it at work where security is quite strict.

Any ideas? Leak in Microsoft Office security?
 

A:Solved: Overrule macro security in Excel and Word with vbs script

K:
The date of the TechNet bulletin you cited is from the year 2001; updated 2003.

The article states: "Microsoft Knowledge Base articles Q306603, Q306604, Q306605, Q306606 discuss href="http://support.microsoft.com/directory/question.asp?sd=gn&fr=0">Microsoft these issues and will be available approximately 24 hours after the release of this bulletin. Knowledge Base articles can be found on the Microsoft Online Support web site."

See,for example: http://support.microsoft.com/kb/306603

http://support.microsoft.com/kb/306604

http://support.microsoft.com/kb/306605

http://support.microsoft.com/kb/306606

I hope this reduces your concerns.

RF123
 

Read other 2 answers
RELEVANCY SCORE 94.4

I have an issue if certain cells are blank (not sure if they would be null, blank, empty) to have a macro to input "0" in the cell instead. When I look at the format in excel it's under general.

I currently have the following and it puts nothing in the cell. (or seems to be nothing in the cell) I can manually enter a "0"(zero) in the cell and it shows.....

Sub Addifblank49()
Sheets("BPS").Select
Range("c16").Select
Var = Selection.Value
If IsNull(Range("C16").Select) Then Var = "0"
End Sub
I've tried using val instead of var and it won't let me. I'm not real familiar with VBA so any help would be awesome!!
 

A:Solved: Excel Macro - enter in data in cell if Null/Empty

Read other 10 answers
RELEVANCY SCORE 94.4

I need to write a macro that will copy data from one spreadsheet into a separate spreadsheet (not in the same file). Here is the scenario...

On the first spreadsheet I have a customer's name in one column and a customer's address in another column. On a separate spreadsheet a have those same customer's names without the addresses. On the second spreadsheet the customer's name may be listed more than once. What I need to do is add the corresponding address to the second spreadsheet beside the name of the customer (identical data from both spreadsheets). The reason copy and paste won't work is because the customer may appear numerous times on the second spreadsheet and their are approximately 50,000 rows of data.

The macro needs to basically say IF Spreadsheet 1, Column A (customer) equals Spreadsheet 2, Column G (customer) then ADD/REPLACE info from Spreadsheet 1, Column B (address) to Spreadsheet 2, Column H (currently blank). I just can't figure out how to make it do that. ANY HELP ANYONE CAN GIVE IS APPRECIATED!
 

A:Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot

Read other 7 answers