Fetching data from cells in excel and place into word 2

[kalle82]

Hi!

Is there a way to fetch data from an open excel document, to another word document?

Lets say I want to fetch an adress and I have made
bookmarks in word accordingly.

In excel the cells b2,c2 and d2 has info about "street name", zip code and city.

I want to get this data into my word bookmarks bookmark1 bookmark2 and bookmark 3?

Ive seen some examples but i dont quite get it?

Is it even possible? Im running at a medium macro security level?

Cheers

Carl

 

[SkipVought]

Have you gone thru the Mail Merge wizard? It's a ONE TIME setup for your document.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Yeah ive gone through it now.

Ive made a fake list in an excel file to sybolize the real.

And I get it to work, with the fake data.

But i need to run the macro. To get the REAL data to show, otherwise there´s nothing to fetch..?

Just an excel file with a macro.

And to fetch a 10 digit number must be placed in cell a2.

There is no big file with all data. We use a standalone application program which the macro contacts... The macro is protected and i se the soruce only use it.

So how do i do to run the macro from a commandbutton in word and also insert the number into the excel file?

 

[SkipVought]

So you have absolutely no visibility to the source data for your addresses? Wow! What a kludge!

Use the CreateObject & GetObject methods to create an Excel application object and open the Excel workbook. See VBA Help for some good examples for using these methods. You will also need to set a reference to the Excel Object Library in the VB Editor Tools > References...

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

how do i do to run the macro
In the Excel VBA help have a look at the Application.Run method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kalle82]

Okay i got the excel app to open Now i need it to select the correct cells.

I found the place but how do i do the selection of sell can i use the macro recorder in excel, and use the expression it give me in word?


Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\test\Adresser_2009.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible
oXL.Visible = True
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here
'get next sheet
Next oSheet

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

 

[PHV]

A starting point:

...
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set oSheet = oWB.Worksheets(1)
oSheet.Range("A2") = [i]yourTextBoxValueHere[/i]
oXL.Run "[i]name of the macro here[/i]"
DoEvents
MsgBox "street name = " & oSheet.Range("B2")
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

Hi kalle82,

Does your UserForm's interface do anything other than solicit the 10-digit #? So far, that's the only thing you've mentioned.

Also, from where does your Excel workbook get the data that go into cells C2, D2 & E2? As I've said before, you don't need any macro code to transfer the data from those cells to Word - all you need is a few fields in the Word document.

Cheers

[MS MVP - Word]

 

[kalle82]

Hi!

Hi kalle82,

Does your UserForm's interface do anything other than solicit the 10-digit #? So far, that's the only thing you've mentioned.

No that the only thing.

Also, from where does your Excel workbook get the data that go into cells C2, D2 & E2?

The excelfile itself contains a macro, which is locked because it accesses stand alone programs to fetch the data. There is no data in the excelfile from the beginning. Only after the macro has been run.

As I've said before, you don't need any macro code to transfer the data from those cells to Word - all you need is a few fields in the Word document.

I need to run the macro to get the data that need to be transferred.


Cheers

 

[kalle82]

PhV were do i put your code

is it here?

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here
'get next sheet
IN HERE?

Next oSheet

 

[PHV]

Does your workbook really have many sheets ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kalle82]

no only 1.

Okay i get the right file to be opened...!

Now i need to take the take from the cells b2 to d2(b2:d2)
to be loaded in my textbox(textbox5)?

Im trying with

But i think i need to select them...Before this is done

'//get data from sheet and write to a VB.NET textbox Control
Textbox5.Text = oSheet.Cells(1, 2).Value

 

[PHV]

Something like this ?
Textbox5.Text = oSheet.Range("B2") & ", " & oSheet.Range("C2") & ", " & oSheet.Range("D2")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kalle82]

Wow! I got i to run the macro from pressing a button in my worduserform!!!

It opens excel and runs the macro.

Now i have to requirements left!

First

How do i make VB to enter the 10digit code in my word userform textbox1 in cell a2 in the excel program?

Second and how do i do to select the cells b2 to d2. and show them in my textbox5?

 

[PHV]

Please, try to make sense with my posts timestamped 7 Apr 09 4:18 and 7 Apr 09 5:07.
All the needed info are there.

 

[kalle82]

PHV!

Worked like a treat!

Okay last requirements.

Placing my 10 digit number in the cell a2 in the excel file.
before running macro.

Is there a function that can make the my macro check if there is any data inside the cells and when it is take it.

That because it take liek 2-5 seconds before the excel macro has retrieved the data.

 

[PHV]

What is YOUR actual code ?

 

[kalle82]

Lol it works!!!!

Damn that was a VERY GOOD JOB!

 

[kalle82]

Actual Code is here! And working code!

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\test\Adresser_2009.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible
oXL.Visible = True
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here

Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set oSheet = oWB.Worksheets(1)
oSheet.Range("A2") = TextBox3
oXL.Run "koren1"
DoEvents
MsgBox "street name = " & oSheet.Range("B2")

TextBox5.Text = oSheet.Range("B2") & ", " & oSheet.Range("C2") & ", " & oSheet.Range("D2")



'get next sheet
Next oSheet

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If




End Sub

 

[PHV]

You may simplify the code like this:

...
oXL.Visible = True
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set oSheet = oWB.Worksheets(1)
oSheet.Range("A2") = TextBox3
oXL.Run "koren1"
DoEvents
TextBox5.Text = oSheet.Range("B2") & ", " & oSheet.Range("C2") & ", " & oSheet.Range("D2")
If ExcelWasNotRunning Then
  oXL.Quit
End If
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

Hi kalle82,

The excelfile itself contains a macro, which is locked because it accesses stand alone programs to fetch the data. There is no data in the excelfile from the beginning. Only after the macro has been run.

So why don't you access the data source(s) directly from Word, instead of going via Excel? It really doesn't make much sense to use Word to run an Excel session to retrieve a set of data when you can do that directly from Word.

The overall process would be much quicker if you accessed the data directly from Word - and you wouldn't then have to wait for Excel, or test the cell contents before continuing.

Cheers

[MS MVP - Word]