Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Open Spreadsheet From Word

I am trying to write a macro in an (existing Word document with bookmarks, etc.) to automatically OPEN a spreadsheet and then update links in the word document. I have set-up the word document to pull information from the Excel spreadsheet as well.

The documents I'm working with are:

"S:Fees_and_Costs_Worksheet.xls" and "DFAFFDBK.doc"

I've tried many of the suggestions posted here and in my VB/VBA reference book but have gotten nowhere...

Any suggestions?


Post your answer or comment

comments powered by Disqus
I use Office 2007 on various OS's.

Trying to open Excel 2003 or 2007 documents in Excel07 from Windows Explorer
takes 1 full minute. Opening Excel 2007 first then clicking the Office
Button | Open | then choosing a spreadsheet opens immediately.

How can you make Office2007 open spreadsheets from Explorer faster??

I checked and this is already un-checked "Ignore other applicatins that use
DDE".

Lots of posts asked how to fix this in Excel2007 when un-checking
"Ignore..." does not work but I couldn't find any answers. Anyone know?
Thank you!!

I've took on a task that I stupidly volunteered myself for since no one else will.. including our development or IT team will seem to get completed.

Here's my dilemma:
User has information in Word that needs to go to XL WorkSheet. It has been formatted with tabs so that the information is copy and pasted into individual cells. To avoid human error, I want XL to open with a fresh workbook so that the information can be pasted into it with the Macro being run from Word.

I've searched everywhere to look for code to open XL up from Word and cannot find anything.

Will it be possible to open a new workbook and paste that information all with a macro from Word? Or will I need to trigger a macro in an already saved workbook??

Can someone please point me in the right direction?

Hi
I know this is probably a basic vb code, but i just struggle so much with working it out.

I want to open my spreadsheet from a UserFrom. I have created the button, and when the user clicks on the button, i want it to open the spreadsheet, DAFees.xls from a mapped networked drive, say n:.

The button is named CommandButton2

thanks.

Hi all,

I managed to get a code for opening an Excel file via Word and then copying and pasting from Word to Excel, but I have an issue. If the Excel file is already open, then the code opens the file again but as a Read Only version. How can I make it paste into the spreadsheet which is already open? If I take the open file line out, then it reports an error at the paste line of the Else clause in the sub.


	VB:
	
) 
    Dim iFilenum As Long 
    Dim iErr As Long 
     
    On Error Resume Next 
    iFilenum = FreeFile() 
    Open filename For Input Lock Read As #iFilenum 
    Close iFilenum 
    iErr = Err 
    On Error Goto 0 
     
    Select Case iErr 
    Case 0:    IsFileOpen = False 
    Case 70:   IsFileOpen = True 
    Case Else: Error iErr 
    End Select 
     
End Function 
 
 
Sub OpenXL() 
     
    Dim Obj1 As Object 
    Dim filename As String 
     
    Set Obj1 = CreateObject("excel.application") 
    Obj1.Visible = True 
    filename = "U:ComplianceSelf-AssessmentFY11SA Returns.xls" 
     
    If Not IsFileOpen(filename) Then 
        Obj1.Workbooks.Open (filename) 
         
        ActiveDocument.tables(3).Cell(1, 2).Range.Copy 
        Obj1.worksheets("sheet1").Cells(2, 2).Paste 
         
         
    Else 
         
        ActiveDocument.tables(3).Cell(1, 2).Range.Copy 
        Obj1.worksheets("sheet1").Paste 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help? Thanks!

J

I am trying to work out how to get data from an excel spreadsheet into a word document.

I have tried the following example from here: http://technet.microsoft.com/en-us/l.../ee692882.aspx

but it fails due to run time error 424 . object required here:

Wscript.Echo objRecordset.Fields.Item("Name"), _ objRecordset.Fields.Item("Number")

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
can anyone advise as to the problem.

thanks

Word Count and Page Numbers do not calculate in status bar when opening a single Word document from a web page

We have a set of Word documents on a web server, with a simple HTML page with links directly to each document. If a document is opened from this page and Microsoft Word is not already open, Word launches as expected but the word count for the document displays as 0 and the page numbers do not appear in the status bar. Making changes to the document on the client does not cause the word count to update or the page numbers to display. Clicking the "Word Count" button on the "Review" ribbon displays the correct word count, but the Status Bar still says the word count is 0.

If Microsoft Word is already open on the client before clicking a link, the Word Count and Page Numbers display correctly. In addition, opening a second document from the same page causes the first document's Word Count and Page Number information to update as expected. All .doc and .docx documents seem to be affected. I've observed this behavior in other scenarios where there is a web link directly to a .doc or .docx file, including in WSS search results.
Why don't the page numbers and word count display correctly in the status bar when opening a single document?

I need to open an excel spreadsheet from within a
VBscript and then run a macro within that spreadsheet
before closing it back down again.
Can anyone provide some code that would do this.

Thanks

Hi,

I'm looking for a method of extracting a Pattern from a word doc and
displaying in an excel spreadsheet. I would like to implement this as a macro
to automate the process.
I was thinking of extracting the data from word using a Perl script, saving
to a txt file and importing into excel.

Is this the optimum way?
Can you run a Perl script within MVB?

Cheers
Drew

I am using Excel 2007. I have a spreadsheet I have saved as a web page. I have links on it to open another spreadsheet in Excel format. The spreadsheet has code that runs when it opens so that it downloads a spreadsheet from the web and pastes the page to the open spreadsheet, then closes the downloaded spreadsheet and saves the new spreadsheet. It runs fine if I open it from windows explorer, but if I open it from the link on the web page I get an error.


	VB:
	
 
Sub auto_open() 
    Sheets("AK0001").Select 
    Workbooks.Open Filename:= _ 
   
"[URL]http://pqrs-efg.hij.ds.xyz.com/reports/rwservlet?cmdkey=paramkey+report=repVariance_ByBuilding_spreadsheet.jsp+desformat=spreadsheet+destype=cache+regionId=10+buildingId=AK0001+IDUSER=1093+file_name=/rwservlet.xls[/URL]"

    Range("A1").Select 
    Windows("rwservlet").Visible = True 
    Windows("Variance_ByBuilding_AK0001.xls").Activate 
    Cells.Select 
    Selection.Delete Shift:=xlUp 
    Windows("rwservlet").Activate 
    Cells.Select 
    Selection.Copy 
    Windows("Variance_ByBuilding_AK0001.xls").Activate 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Windows("rwservlet").Activate 
    ActiveWindow.Close 
    Range("A1").Select 
    Application.DisplayAlerts = False 
    ChDir "P:caddSDM 2012 ReportsNSCAK0001" 
    Dim strPath As String 
    Dim strFileName As String 
    strPath = "P:caddSDM 2012 ReportsNSCAK0001" 
    strFileName = "Variance_ByBuilding_AK0001.xls" 
    ActiveWorkbook.SaveAs Filename:=strPath & strFileName, FileFormat:=xlNormal 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Error is Run-time error '1004':

Cannot access 'Variance_ByBuilding_AK0001.xls'.

If I hit End it finishes the routine and the spreadsheet is updated, if I hit debug it highlights the last line for the SaveAs.

Hi

I'm having a problem when I try to open a spreadsheet from Access 2003.

The Excel Spreadsheet contains some links to external data and also a
column that contains a user defined function.

If I use the following code in Excel VBA the spreadsheet opens without
a problem and I can see values in the column with the user defined
function

Workbooks.Open(fileName:="File Location and Name", UpdateLinks:=0,
ReadOnly:=True)

however if I use the exact same code in Access VBA ie.

Set objWorkbook = objExcel.Workbooks.Open(fileName:="File Location and
Name", UpdateLinks:=0, ReadOnly:=True)

it opens the spreadsheet and my column with the user defined function
contains #NAME? for every cell.

I've tried a load of different ways to open it from Access but the same
problem happens everytime.

What does Excel do differently that allows the spreadsheet to open as
Read-Only with the correct values displayed in that column?

Hi,

I have recently just used this macro for my excel spreadsheet to pull data from all 50+ forms within my "Engineer Forms" folder.

For some reason I am receiving a Error 53 : File not found message.

It is pulling the data from one document in my folder but not the rest.

Can someone please assist me on this???

Sub Log_Docs()

Dim WdApp As Object, WdDoc As Object, blnAppOpen As Boolean, strDocName As String
Dim lngWriteRow As Long, strDir As String

blnAppOpen = True
strDir = "P:Engineer Forms"

If MsgBox("Data will be retrieved from all Word documents in this folder:" & vbCrLf & vbCrLf & _
strDir & vbCrLf & vbCrLf & "Do you want to continue?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If

' Look for first word document
strDocName = Dir(strDir & "*.doc")

' If no word document found then exit
If strDocName = "" Then
MsgBox "No files found!", vbCritical
Exit Sub
End If

On Error GoTo ErrorHandler
' Get open instance of word application if available
Set WdApp = GetObject(, "Word.Application")

Do While strDocName <> ""

' Open word document
Set WdDoc = WdApp.Documents.Open(strDir & strDocName)

With Sheets("TI_Information")
' Find last completed row on reporting log
lngWriteRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

' Populate worksheet
.Range("A" & lngWriteRow).Value = WdDoc.FormFields("FldSiteID").Result
.Range("B" & lngWriteRow).Value = WdDoc.FormFields("FldDate").Result
.Range("C" & lngWriteRow).Value = WdDoc.FormFields("Fldname").Result
.Range("D" & lngWriteRow).Value = WdDoc.FormFields("Fldsname").Result
.Range("E" & lngWriteRow).Value = WdDoc.FormFields("Fldtype").Result
.Range("F" & lngWriteRow).Value = WdDoc.FormFields("Fldresult").Result
.Range("G" & lngWriteRow).Value = WdDoc.FormFields("Fldkit").Result
.Range("H" & lngWriteRow).Value = WdDoc.FormFields("Fldstatus").Result
.Range("I" & lngWriteRow).Value = WdDoc.FormFields("Fldref").Result
.Range("J" & lngWriteRow).Value = WdDoc.FormFields("Fldbt").Result
.Range("K" & lngWriteRow).Value = WdDoc.FormFields("Fldmc").Result
.Range("L" & lngWriteRow).Value = WdDoc.FormFields("Fldlate").Result
.Range("M" & lngWriteRow).Value = WdDoc.FormFields("Fldmiss").Result
.Range("N" & lngWriteRow).Value = WdDoc.FormFields("Flddelay").Result
.Range("O" & lngWriteRow).Value = strDocName
.Range("P" & lngWriteRow).Value = Now

End With

' Close word document and move to Logged folder
WdDoc.Close SaveChanges:=False
Name strDir & strDocName As strDir & "Logged/" & strDocName

' Look for next word document
strDocName = Dir

Loop

Set WdDoc = Nothing
If blnAppOpen = False Then
WdApp.Quit
Set WdApp = Nothing
End If

Exit Sub

' Error Handler
ErrorHandler:
If Err.Number = 429 Then
'Word is not running, open Word with CreateObject
Set WdApp = CreateObject("Word.Application")
blnAppOpen = False
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
End If

End Sub

I first just want to know if this is even possible...

We use a web-based, lead management system to run our leads for our company. One of the tedious things that our reps must do is fill out an Excel spreadsheet that is sent to our underwriters for approval. This is a fairly complex, pre-formatted spreadsheet.

With this lead management system, we are able to upload a pre-formatted Word doc that, when opened, can populate itself based on the data for a specific lead. This is great for custom contract creation.

I would like it if Excel could do the same thing.

So far, the only way I have been able to see it happening would be to use the Import from Web feature that Excel has... this would allow the user to navigate to the website, login, go to the specific lead, select a table, import the data... then set up the Excel spreadsheet to auto-populate all that data over to the pre-formatted tabs.

This is do-able... but still rather cumbersome.

Ideally, I would like something similar to Word... where we just upload the doc, then we are able to open it from the LMS and it populates itself immediately.

Does anyone know if something like this is possible to program with Excel??

HI friend,

Thanks for all your help for previous threds.

Can any please tell me how to write a code where i can open Excel2003 file from Word2003 and delete data from Sheet1 and then close the excel2003.

All this is done from Word file.

Thank you.

Hi,

I have recently just used this macro for my excel spreadsheet to pull data from all 50+ word document forms within my "Engineer Forms" folder.

For some reason I am receiving a Error 53 : File not found message.

It is pulling the data from one document in my folder but not the rest.

Can someone please assist me on this???

Sub Log_Docs()
 
Dim WdApp As Object, WdDoc As Object, blnAppOpen As Boolean, strDocName As String
 Dim lngWriteRow As Long, strDir As String
 
blnAppOpen = True
 strDir = "P:Engineer Forms"
 
If MsgBox("Data will be retrieved from all Word documents in this folder:" & vbCrLf & vbCrLf & _
 strDir & vbCrLf & vbCrLf & "Do you want to continue?", vbYesNo + vbQuestion) = vbNo Then
 Exit Sub
 End If
 
' Look for first word document
 strDocName = Dir(strDir & "*.doc")
 
' If no word document found then exit
 If strDocName = "" Then
 MsgBox "No files found!", vbCritical
 Exit Sub
 End If
 

On Error GoTo ErrorHandler
 ' Get open instance of word application if available
 Set WdApp = GetObject(, "Word.Application")
 

Do While strDocName <> ""
 
' Open word document
 Set WdDoc = WdApp.Documents.Open(strDir & strDocName)
 
With Sheets("TI_Information")
 ' Find last completed row on reporting log
 lngWriteRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

 ' Populate worksheet
 .Range("A" & lngWriteRow).Value = WdDoc.FormFields("FldSiteID").Result
 .Range("B" & lngWriteRow).Value = WdDoc.FormFields("FldDate").Result
 .Range("C" & lngWriteRow).Value = WdDoc.FormFields("Fldname").Result
 .Range("D" & lngWriteRow).Value = WdDoc.FormFields("Fldsname").Result
 .Range("E" & lngWriteRow).Value = WdDoc.FormFields("Fldtype").Result
 .Range("F" & lngWriteRow).Value = WdDoc.FormFields("Fldresult").Result
 .Range("G" & lngWriteRow).Value = WdDoc.FormFields("Fldkit").Result
 .Range("H" & lngWriteRow).Value = WdDoc.FormFields("Fldstatus").Result


I am trying to use Word documents for mail merges that differ depending upon
the state to which they will be sent. I currently select the state in an
Excel worksheet from a combo box that translates the state abbreviation to a
number from 1 to 50. I use a vlookup function to convert the chosen state to
the appropriate file and path name. I need to know how to use the results of
the vlookup cell to open the indicated Word document.
--
Confused, but learning.

Currently we have a macro in word which selects a specifc table in the document ready to be copied to excel.

Im trying to automate this process by creating a macro in excel which will open the word file automatically and than copy the specifc table from the word document and paste it into the excel spreadsheet.

I have gone as far as opening the word document successfully but when i have copied over the code from word document macro and pasted it into the excel macro it does NOT work Debug error " run time error 450 Wrong number of arguements or invalid property assignment"

The following code is in yellow when i click on debug

Sub Open_Word_Document_&Paste()

Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="D:PROFILESiltafrMy Documents31AA.doc"

Selection.Find.ClearFormatting
With Selection.Find
.Text = "project code"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.Copy
Selection.Find.ClearFormatting
With Selection.Find
.Text = "overall status"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.PasteAndFormat (wdPasteDefault)
Selection.Tables(1).Select
' Remove all carriage returns - added 16/04/2007
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Tables(1).Select
Selection.Copy

End Sub

Please note I have done the macro upto the copying , once that works I will move onto paste active.paste shud do the trick. Anyway who knows how to fix the debug and enter the line of code that will paste it into the excel worksheet that would be great . Im new to this but very keen to learn

I have an auto_close macro in a spreadsheet. When I open the spreadsheet directly it executes perfectly on closing it. However, when the spreadsheet is opened from another macro this code does not execute.

Is there an option I can set to ensure it runs, or do automacros never execute from another workbook?

Many thanks.

L

I am invariably typing text into Excel for Mail Merge Lists and would love to
just type straight and convert the text using a Change Case Funtion such as
you find in Word (Format Change Case) in order to have the proper Caps so the
finish document comes out clean.

I think that the flow of data for long mail merge spreadsheets would be
significantly easier for the Data Input Person if MS would just "cut and
paste" this function into Excel from Word.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Hi,

I used to have a macro that downloads another spreadsheet from a URL.

	VB:
	
urlString = "www.url.com?txtday=5&txtyear=2008" 
Workbooks.Open Filename:= urlString 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The Url used to generate a spreadsheet based on the parameters in the URL and opened up the spreadsheet directly. This was working very well. But now they changed it so that the URL generates the spreadsheet and pops up a Save as or Open dialog box.

This change broke my macro. I am not sure how to handle this. Is there a way I can have the macro save it and then open it? Since its not a straight forward path like http://url.com/ab.xls, but is a url that generates a workbook makes it more complex to me. Any help is greatly appreciated

Thanks,
Prakash

argrrrmmmnnnn,

Ok, this is something daft & windows orientated....

I've had a new machine, and for some reason if i try and open excel files by double-clicking on a .xls file, excel tries to open each part of the filename individually!!!!

For eg, if i have a file called week 1 report.xls, and i double click that, it gives me 3 messages saying:

cant find c:filepathweek.xls
cant find 1.xls
cant find report.xls

I can get round it by going into excel first, and opening the files from there but it's a right head-doer!!!

I'm on windows XP, and using excel 2003 to open the files...

Hello to everyone.
I am trying to get from Word some information from excel; namely I need to sense if a specific Workbook is currently open in the existing instance of excel, then close it.
I tryed using this code to switch to excel
Code:
Set XLapp = GetObject(, "Excel.Application")
XLapp.Visible = True
but it create a new instance of excel rather than switching to the current one, where my target file is not (cannot?) be located.

Please, give me a hint!
Thank you.

Hi
I'm trying to automate a report in Word that would copy a specific range from Excel and special paste it back to Word (a linked table). I'm able to open the spreadsheet from word, and even copy the desired range, but when I try to special paste it I get an error
"Run-time error '5345':
Word cannot obtain the data for the {000000-000000-000000} link."
(the number in brackets in the message is different)

Additional info:
* When I try to paste it regularly (not special paste), it works fine.
* If I manually go to my Excel spreadsheet, select and copy my range and special paste it in Word it works fine.
* My code:

Sub CopyExcelTable()
Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "D:Documents and Settings...ReportTablesCopy.xls"
appexcel.Visible = True
appexcel.activeworkbook.sheets("Test").Range("tabl").Copy
End Sub

Sub PastingLinkedTable()
Selection.PasteExcelTable True, False, False
End Sub

Note that I'm trying to import a table from Excel to Word, and not export a table from Excel to word.

Hi

I can open spreadsheets from within Excel in the usual way. However if I try to open a file by double clicking on it Excel opens but the individual file does not.

Any suggestions please

I have a word document that i receive every month from my data processing company that i transfer (via cut and paste) to an excel spreadsheet manually. I will start with some info on both the word and excel spreadsheet that i made. I am using Ms office 2003

Word doc
-contains 9 columns (first one is dates and the other 8 contains dollar values)
-Each of the 9 columns has rows that represents a day within the month where there were transactions.
-Each client is on a new page within that same document

My excel spreadsheet
-contains 9 columns (first one is dates and the other 8 contains dollar values)
-Each of the 9 columns has rows that represents a day within the month where there were transactions.
-Each client is on a new page within that same sheet
-Has formulas at the bottom of each column

I would like to know if possible to have a macro that does the cut and paste and formatting for me for each client from word to excel


No luck finding an answer? You could always try Google.