Posts by alan.welsh

Thanks for all your replies.

I have tried two of Ritchie's solutions and they work fine on a new worksheet, however I have saved the macros to a personal workbook in an alternative startup location on our Network. I have imported a text file and tried to run them on that and it doesn't work!

Do you have any idea why it doesn't work?

I noticed that you used 'With Sheet1'

Because I have imported from a text file I have to rename the sheet to Sheet1, I don't know if this affects anything!

Can you help me please?

I have a spreadsheet with the following macro.

lastrow = Cells(Rows.Count, "A").End(xlDown).Row

Do
On Error GoTo ErrorHandler
Cells.Find(What:="Account", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

Selection.EntireRow.Delete
Cells.FindNext(After:=ActiveCell).Activate
Selection.EntireRow.Delete

Loop Until lastrow

End Sub

_________________________
The code is to search for the word account and delete the row. I want to loop this until all rows containing the word 'Account' are deleted.

I keep getting a runtime 91 error. Can anyone help me ?

Can you tell me how I can format a value as a percentage within a concatenated string?

I have the following formula: -

=CONCATENATE(A5," ",DOLLAR(ROUNDDOWN(K5,0),0),A6," ",K6)

K6 is 15.59%, but when I reference it within the text string, it returns 0.1559

Can anyone help please?

Thanks a lot, I used the DOLLAR function as shown below with the 0 at the end for no decimals.

Cheers

=CONCATENATE(A5," ",DOLLAR(ROUNDDOWN(K5,0),0))

Is it possible to format part of a concatenated string?

For instance I have the following formula

=CONCATENATE(A5,G5)

This returns the following

Sales have decreased by -7260.39

The -7260.39 is the value of cell G5.

I want to be able to format this as Currency with no decimal points. However when included in the Concatenate string it returns the value as text. Is there anyway I can format it within the Concatenate string?

See my code below.
Dim your bookmarks as Excel.Range
Set the variable to the cell reference
Then use 'InsertAfter' to assign the variable value to the bookmark.
Hope this helps!

Sub createWordReport()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim companyName As Excel.Range
Dim yearEnd As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

Set myDoc = wdApp.Documents.Add(Template:="T:\Templates\test\Accounts Report.dot")

Set companyName = Sheets("Data").Range("A7")

Set yearEnd = Sheets("Data").Range("C7")

Worksheets("Charts").ChartObjects("Chart 12").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

With myDoc.Bookmarks
.Item("CompanyName").Range.InsertAfter companyName
.Item("YearEnd").Range.InsertAfter yearEnd
.Item("TurnoverChart").Range.Paste
End With

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub

I have the following code which creates a Word report from an Excel Spreadsheet. I want the report to contain data from the spreadsheet. I have set a Bookmark in the Word template named "CompanyName". I want this to be filled with the value of cell "A7" from a sheet named "Data". Can anyone tell me how I can do this? Please Help!

Sub createWordReport()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="T:\Templates\test\Accounts Report.dot")

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub

Thanks

I have set up a Management Information system with a button that creates a powerpoint presentation with the data from excel. Can anyone tell me how I can insert a range of cells ("A7:F7") into the presentation. Can only get one cell in ("A7")

Help Please

Or has anyone any refrences to code of this sort of thing

No, I'm looking to create a powerpoint presentation from the click of a button (using VBA) with the presentation being based on data and charts from excel. I think it's called 'Automation'. I create the presentation, but not surehow to include charts etc from excel. Can anyone help?

Can anyone help me to create a powerpoint presentation with charts from my excel workbook?

Cheers, I used your method using the button on the control toolbox as opposed to the other one.

Is there any way that you can assign 2 macros to one button?

Thanks alot. Got it to work today at work. I had left the data path in to the database where I was selecting the data from tables. I removed this and it works fine. Exactly what I was wanting. No doubt I'll require your assistance again for something else! Cheers!!!!:cheers::cheers:

test.xls

I tried using the recorder and changing the part you said, but it still won't work. I've uploaded the database file and will upload the excel file in a min. You'll need to create the following directory: C:\Program Files\Viztopia32\R281\
and place the RA.mdb in there.

Hope you can get it to work. Thanks!

Sorry, I can't get it to work. I'm totally new to VBA, so not sure what I'm doing. I changed the path to the database, but it's still not working. Not sure what to do! Can you be of any further assistance?

There's a syntax error with the following, can you help please?

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & Range("FileName").Value & ".mdb;DefaultDir=C:\Will\Access;DriverId=25;FIL=MS Access;MaxBuffe"

Thanks a lot, I think this is exactly what I'm looking for. I'm developing a Management Information system for multiple clients. The data is in T:\Viza\Clientcode\clientcode.mdb, therefore now I can let the user of my system change the client codes and the correct data will be used in the system.
I'll try it tomorrow @ work. Cheers for the prompt reply!

Later!

It is linked to an Access DB using MS Query. But I want to be able to change the datasource via a Macro. Can You help please?

I have linked my Excel spreadsheet to an Access database. I want to be able to select the datasource via a macro. Is this possible? If so, can anyone help me how to do this? I'm new to VBA!

Thanks