Posts by BeginnerBob

Re: Schedule Issue

If I used windows scheduler then when the workbook opens normally the security will ask whether to enable or disable the macros.

Won't windows scheduler automatically disable the macro, or will I have to play around with the security for that the xls has a low security setting?

Re: Schedule Issue

Cheers for the link. However, I have a few questions:

The first problem will be how to kick-off the OnTime Method. This we can do via the Workbook Open Event. The fastest way to get to the Private Module of the Workbook Object (ThisWorkbook) is to right click on the Excel icon next to "File" and select "View Code"

Private Sub Workbook_Open()
     Application.OnTime TimeValue("15:00:00"), "MyMacro"
End Sub

Does this mean that the workbook has to be opened for the OnTime method to work if it fires off on the workbook open event?

Hi there I need a macro to run at 6 o'clock in the morning every Friday. How would I go about doing this?

Do I need a windows scheduled task, use onTime event, use a DTS package.

I am unsure at present.

Thanks

Bob

Re: Shared spreadsheets

Thanks for your timely response.

However, what happens if two users want to be in the spreadsheet at the same time, how would I approach this.

Hi there I have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.

Can anyone give any advise.

Thanks

Bob.

This code returns 0, but the actual value is 40. Does anyone know why.

=MIN(IF((Data!$AF$1:$AF$65000="Balanced")*(Data!$U$1:$U$65000="Normal - Invoice awaiting release"),Data!$AL$1:$AL$65000,0))

This is an array formula so ctrl, shift and enter is needed.

Does anyone know any other formulas to use.

Re: Template Issue

no .doc

Should I save the word book as a .dot.

Why would this be benefical?

Re: Template Issue

My bookmarks are Text Form Fields therefore they are grey boxes.

I want to use this as a template so you can re populate the bookmarks as many times as you wish without closing and opening the document.

Therefore, would it be best to copy the document to a new document and populate the bookmarks from there so that the bookmarks stay?

Hi there I was hoping to get your advise on word templates.

I have a template which is covered in bookmarks. These bookmarks are populated from a SQL database.

Which is the best way to run this. A command button on the same word document, a menu button ....?

Once the bookmarks are populated they cannot be repopulated. Is there any way around this.

Cheers

Bob.

I have a listbox with values in such as 2003, 2001. How do I sort these values in the list box?

Hi,

I'm new to Word Macros. However, I have a template that needs to be populated via a form. I was wondering what is the best way to do this, shall I place the text from the form into text fields on the document, these text fields must contain an address block, name etc.

Cos in excel its simple just place the value of the text box into a cell. However, I am unsure how to approach this.

Hi there In a cell in a spreadsheet I want to use a formula to delete 3 months from a date to get a new date. In Excel formulas there isn't a dateadd function so any ideas?

eg. 01/01/2001 - 3 months = 01/11/2000.

The solution must be anExcel formula and not VBA code.

Cheers.
Bob

Hi there I have a calendar control on my form in a frame with 2 text boxes.

What I want to do is to click in one of the text boxes and then click on the calendar and the date will automatically be placed into the text box. Therefore, calendar click event must remember which text box was clicked.

Does anyone know how to do this?

Hi there I am using the function CopyFromRecordset to return information to my spreadsheet from a SQL Database. However, when the fields in my database are longer than 255 characters the field in excel is only equal to 254.

Is there any way to get around this problem.

Cheers.

Bob

Re: Saving work

Hi there, Cheers for that. However, when I save this spreadsheet as a txt file the columns go all out of sink due to missing data.

Therefore, if I populate the missing data with zeros the txt file is fine. However,these values need to be spaces, so do you know how to automate the opening of a txt file and replacing space 0 space with " ".

cheers

Hi there,

I have never tried saving an excel spresdsheet to a text file.

I have 3 colums in an excel spreadsheet such as

Bob 12/11/05 January
Brian 15/05/09 February
Dave 01/11/07 December

These 3 columns which can be many rows long need to be saved in a text file. However, the first column needs to be padded by 20 characters, the second to 8 characters and the last to 25 characters.

Can this be done in Excel?

Re: Copy selected columns to new sheets

I would put the names into an array and then loop through them and copy each set onto the templete such as,

Dim RepArray As Variant
Dim Flag As Boolean
Dim counter As Integer


counter = 0
ReDim RepArray(0)


For i = 6 To Worksheets("Sheet1").Range("C65536").End(xlUp).Row
    Flag = False
    For j = LBound(RepArray) To UBound(RepArray)
        If Cells(i, 3).Value = RepArray(j) Then
            Flag = True
            Exit For
        End If
    Next j
    If Flag = False Then
        RepArray(UBound(RepArray)) = Cells(i, 3).Value
        ReDim Preserve RepArray(UBound(RepArray) + 1)
        counter = counter + 1
    End If
Next i




'Main loop
For j = LBound(RepArray) To UBound(RepArray)
    For i = 6 To Worksheets("Sheet1").Range("C65536").End(xlUp).Row
        If Cells(i, 3).Value = RepArray(j) Then
            ' do your stuff here
        End If
    Next j
Next j

Hi there,

I have many sheets in a workbook. Each of these sheets are called slide8, slide11, slide12 etc. On the sheets there are graphs which need to be pasted onto a powerpoint presentation under the corresponding slide.

My code looks like:

Dim objPPT As Object
Dim objPrs As Object
Dim shtTemp As Worksheet
Dim chtTemp As ChartObject
Dim intSlide As Integer

Set objPPT = CreateObject("Powerpoint.application")
objPPT.Visible = True
objPPT.presentations.Open ThisWorkbook.Path & "\template.ppt"

For i = 1 To Sheets.Count
a = Mid(Sheets(i).Name, 6, Len(Sheets(i).Name) - 5)

objPPT.ActiveWindow.ViewType = 1

For Each shtTemp In ThisWorkbook.Worksheets
For Each chtTemp In shtTemp.ChartObjects

chtTemp.CopyPicture

objPPT.ActiveWindow.View.GotoSlide Index:=objPPT.presentations (1).Slides.Add(Index:=a, Layout:=1).SlideIndex
'
objPPT.ActiveWindow.View.Paste
Next
Next
Next i

Aplication.DisplayAlerts = False
objPPT.presentations(1).SaveAs Filename:=ThisWorkbook.Path & "\tester.ppt"
Aplication.DisplayAlerts = True

objPPT.Quit

Set objPrs = Nothing
Set objPPT = Nothing

However, I can not select the slide which I want the graph to be pasted on.

Can anyone help???

I want to put the values of column A into an Array.

However, Column A is likely to change, for example

Dim DataArray as variant

For i = 1 To Range("A65536").end(xlup).row
cells(i,1).select
'Add the values into the Array DataArray
Next i

Can anyone help?

Is there a way of opening powerpoint from Excel, going to a particular slide which already has a chart and modifying that chart with the data from Excel via the datasheet in powerpoint e.g importing the data from Excel to the datasheet.

:rock: