PDA

View Full Version : User Selected Excel File



Kindly_Kaela
01-11-2007, 08:56 AM
Hi....

I'm making a proposal generator for my company and it finally comes time to incorporate pricing. We use MicroSoft CRM, but unfortunately I have no clue how to use SQL and generate the proposal with that data. But I think I found a work around.

The end-user can export pricing from CRM into a 'Data-Only' Excel spreadsheet and save it to their harddrive. I believe I can program the Proposal Generator to grab the pricing data from Excel and populate the Word proposal with sexy tables.

Finally my question...

What code will bring up the OPEN FOLDER window so the end-user can find the Excel spreadsheet on their harddrive, double click it, and then my 'Proposal Generator' absorbs the data from Excel and populates Word?

Thanks!
Kaela
:cloud9:

P.S. If instead, anyone has advise on how I can easily learn how to pull data from SQL, I would be VERY grateful!!! :mkay

Kindly_Kaela
01-11-2007, 09:15 AM
I tried recording a Word macro to find the code for copy / pasting from Excel to Word. However, as you can see it's not specifying which excel spreadsheet or anything. Looks like I'm going to need quite a bit of help on this objective.



Sub Pricing_02()
'
' Pricing_02 Macro
' Macro recorded 1/11/2007
'
Selection.GoTo What:=wdGoToBookmark, Name:="PriceNRC"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.MoveDown Unit:=wdLine, Count:=2
Selection.PasteExcelTable False, False, False

End Sub

Bob Phillips
01-11-2007, 09:24 AM
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open Filename:=.SelectedItems(1)
End If
End With

Kindly_Kaela
01-11-2007, 09:29 AM
XLD, the window comes up, I select the file, but then get an error on

Workbooks.Open FileName:=.SelectedItems(1)

In case you don't know, I'm fairly new to VBA and may need a kind walk through. Sorry.




With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open Filename:=.SelectedItems(1)
End If
End With

Bob Phillips
01-11-2007, 09:37 AM
I know that ma'am, but I did test it first.

My first thought was that your Excel version didn't support FileDialog, but that would (should?) have failed earlier.

See if this code works for you



Dim sFile
sFile = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
If sFile <> False Then
Workbooks.Open Filename:=sFile
End If

Kindly_Kaela
01-11-2007, 09:41 AM
Error on this line...

.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")

Bob Phillips
01-11-2007, 09:47 AM
What is going on? Can you post a workbook that errors?

What Excel version and OS?

Kindly_Kaela
01-11-2007, 09:55 AM
Excel 2003
Word 2003
Win XP

I'm not sure what a workbook that errors is? I'll try and describe exactly what happens. But also, keep in mind my VBA program is in WORD.


Sub Pricing_04()

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Workbooks.Open FileName:=.SelectedItems(1)
End If
End With

End Sub


The above code produces a run-time error '424', object required. When I hit 'debug', it highlights: Workbooks.Open FileName:=.SelectedItems(1)


Sub Pricing_05()

Dim sFile
sFile = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
If sFile <> False Then
Workbooks.Open FileName:=sFile
End If

End Sub


The above code says 'compile error: method or data member not foudn'

Bob Phillips
01-11-2007, 10:09 AM
Word! That explains it. I thought you were opening the data in Excel and creating a Word document from there.

In Word it is



Sub Pricing_04()

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Documents.Open FileName:=.SelectedItems(1)
End If
End With

End Sub


but opening an Excel workbook in Word will just give gibberish.

Why don'y you do it all in Excel, it is far simpler IMO, and unless you have some fancy styles, Excel can do a decent job of formatting. I do all my invoicing in Excel for example.

BTW, SQL may be the better way to go but waht is Microsoft CRM?

Kindly_Kaela
01-11-2007, 10:14 AM
I haven't tried the code yet, but let me answer some of these questions.

I do have some very fancy styles. These are 30 page full blown-out proposals that will be generated for our customers. Pricing is 2 of the pages. There will be about 20-30 different proposals to choose from and tons of Word editting behind the scenes depending on the users choices. Can I do all that in Excel?

Microsoft CRM is an application that records customer information that our Sales Reps enter and includes tons of reporting. It uses MS SQL as the database for storing data. I would love to understand how to pull data out of SQL and populate Word....but I'm absolutely clueless about SQL (and VBA, lol). But I have the proposal WORD part done....it's the pricing that would make this a great success. Pricing is in SQL...or it can be exported to EXCEL by the user.

Kindly_Kaela
01-11-2007, 10:19 AM
Yea, that code produced gibberish in WORD. I don't want to open EXCEL in word. I want to grab data out of EXCEL and paste it into Word.

Is that possible?

Bob Phillips
01-11-2007, 10:27 AM
Let's step back a bit.

First, I think it is best to continue doing it in Word. Excel could (possibly) do a 30 page proposal, but shouldn't IMO. I would assume that you have a proposal template already pre-formatted?

I presume that as you are doing it in Word, that you have particular points at which you wish to insert the information, whether that comes from a database or from Excel, so have ypu created placeholders for that data (bookmarks is usually easiet in Word IMO)?

Next, do you want to go the SQL route, or would you rather stick with Excel at this point in your learning curve. If the former, it should all be done from Word IMO. If the latter, I would do it from Excel, and open an instance of Word from there, open the proposal document/template, and just drop the Excel data into the placeholders.

Kindly_Kaela
01-11-2007, 10:32 AM
Great questions...

I would prefer to learn SQL because then it would be so much easier for thte user. Otherwise, they have to export pricing from CRM into Excel and then Import Excel into my Proposal Generator.

What do you think I should do?

stanl
01-11-2007, 10:41 AM
This may appear a bit at first glance, but just looking at the VB code can give you an idea of how to proceed extracting SQL data and inserting into a Word Document.

http://msdn2.microsoft.com/en-us/library/aa192487(office.11).aspx

.02 Stan

Bob Phillips
01-11-2007, 10:42 AM
That makes good sense. In that case, could I respectfully suggest that you re-post the question in the Word forum. Whilst I can do the SQL, they will be better at the Word bits than I would, such as inserting the data in the correct part.

Give them a good description along the lines of wanting a Word app to query an MS-SQL database and to pull back the data into a 30 page proposal. Tell them about the data structure (tables, columns, etc.) what you want to pull back, where in the doc it will go etc.

I know the boys over ther aren't as good as us in the Excel group :devil2:, but they should sort you out. If you don't get a response soon, post back and I am sure we can sort you out (I might even do some playing myself in the meantime).

Kindly_Kaela
01-11-2007, 10:47 AM
Sounds good, I'll post in WORD forums shortly. As soon as I'm done reading through this massive guide Stan sent me....hehe.

Thanks!!
:love

Bob Phillips
01-11-2007, 10:48 AM
Don't bother reading that, it is too much for you at this stage, and it is .Net.

Kindly_Kaela
01-11-2007, 11:10 AM
Here's what I posted in the WORD section of this forum...

http://www.vbaexpress.com/forum/showthread.php?p=85877#post85877

Bob Phillips
01-11-2007, 11:14 AM
That should get you started. They should be able to give you SQL code but I am sure that they will want to know if the DB is password protected ( and the PW if so), the table and column names for the data that you are interseted in, as well as where you will drop the data in the Word doc (have you already created bookmarks as I mentioned?). If you collate this info whilst they are ramping up, you will stay ahead of the game.