Over 1 million tech questions and answers.

Excel Macro - converting excel to word doc - stops after row 29

Q: Excel Macro - converting excel to word doc - stops after row 29

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X ITAC") > 0 Then ' ITAC
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "ITAC"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X ITSC") > 0 Then ' ITSC
OptChecked = True
Else
OptChecked = False
End If
WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "ITSC"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With
If InStr(1, UCase(ActiveCell.Text), "X ITSG") > 0 Then ' ITSG
OptChecked = True
Else
OptChecked = False
End If
WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "ITSG"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

'CheckBoxes
CurRange = "G" & CurRow & ".." & "G" & CurRow
Range(CurRange).Select

WrdApp.Selection.MoveRight Unit:=wdCell

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Approvals:" & vbCrLf

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X GIS EXPENSE") > 0 Then ' GIS EXPENSE
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "GIS Expense"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 72.75
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X PPO#") > 0 Then ' PPO #
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "PPO #"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 58.6
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeText Text:=" "
Set TxtObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.TextBox.1")

With TxtObj.OLEFormat.Object
.Font.Name = "Arial"
.Font.Size = 8
.Width = 31.5
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph
Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X AR#") > 0 Then ' PSR #
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "PSR #"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 58.6
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeText Text:=" "

Set TxtObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.TextBox.1")

With TxtObj.OLEFormat.Object
.Font.Name = "Arial"
.Font.Size = 8
.Width = 31.5
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph
WrdApp.Selection.TypeParagraph

With WrdApp.Selection
.Font.Name = "Arial"
.Font.Size = 8
.Font.Italic = True
.Font.Bold = False
.TypeText Text:="(Fill in appropriate PPO and/or PSR#s)"
End With

'PPO STATUS
CurRange = "H" & CurRow & ".." & "H" & CurRow
Range(CurRange).Select

WrdApp.Selection.MoveRight Unit:=wdCell

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="PPO Status:" & vbCrLf

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X INITIATING") > 0 Then ' INITIATING
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Initiating"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X SUBMITTED") > 0 Then ' Submitted
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Submitted"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X IN-PROCESS") > 0 Then ' In-Process
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "In-process"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X COMPLETED") > 0 Then ' Completed
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Completed"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), "X NON-PPO INITIATIVE") > 0 Then ' Non-PPO Initiative
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Non-PPO Initiative"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph
WrdApp.Selection.TypeParagraph

With WrdApp.Selection
.Font.Name = "Arial"
.Font.Size = 8
.Font.Italic = True
.Font.Bold = False
.TypeText Text:="(Select status based on status with PPO)"
End With
'ALIGNMENT LEVEL
CurRange = "I" & CurRow & ".." & "I" & CurRow
Range(CurRange).Select

WrdApp.Selection.MoveRight Unit:=wdCell

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Alignment Level:" & vbCrLf

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), UCase("X Aligned to Strategic Initiatives")) > 0 Then ' Aligned to Strategic Initiatives
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Aligned to Strategic Initiatives"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 165
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), UCase("X Aligned to Belt Tightening")) > 0 Then ' Aligned to Belt Tightening
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Aligned to Belt Tightening"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 165
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), UCase("X Aligned to Executive Sponsor")) > 0 Then ' Aligned to executive sponsor
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Aligned to executive sponsor"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 165
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set ChkObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.CheckBox.1")

If InStr(1, UCase(ActiveCell.Text), UCase("X Aligned to Non-PPO Initiative")) > 0 Then ' Aligned to Non-PPO initiative
OptChecked = True
Else
OptChecked = False
End If

With ChkObj.OLEFormat.Object
.Caption = "Aligned to Non-PPO initiative"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
.Width = 165
End With

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph
WrdApp.Selection.TypeParagraph

With WrdApp.Selection
.Font.Name = "Arial"
.Font.Size = 8
.Font.Italic = True
.Font.Bold = False
.TypeText Text:="(Select appropriate or work effort alignment)"
End With

End Sub
Public Sub CopyWorkSheet(WkSheet As Worksheet, WkBkTo As Workbook, WkBkFrom As Workbook, ByRef CurRow As Integer)
Dim EndInt As Long
Dim X As Long
Dim CurRange As String
Dim ChkRange As String
Dim PasteRange As String
Dim MCellData As String

WkSheet.Activate
Range("A1").Select
Selection.End(xlDown).Select
EndInt = Application.ActiveCell.Row + 1

For X = 2 To EndInt

frmStatus.lblProcess.Caption = "Processing WorkSheet " & WkSheet.Name
frmStatus.lblStatus.Caption = "Processing item " & Format(X, "###,###,##0") & " of " & Format(EndInt, "###,###,##0")
frmStatus.Repaint

WkSheet.Activate

'Check to see if column R has an X in it.
ChkRange = "R" & X & ".." & "R" & X
Range(ChkRange).Select
MCellData = Application.ActiveCell.Value

If UCase(MCellData) = "X" Then
CurRange = "A" & X & ".." & "Q" & X
Range(CurRange).Select
Selection.Copy

WkBkTo.Activate
PasteRange = "A" & CurRow & ".." & "A" & CurRow
Range(PasteRange).Select

Application.ActiveCell.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

CurRow = CurRow + 1

End If
Next
End Sub
Public Sub CopyWorkSheets()
Dim WkBkFrom As New Workbook
Dim WkBkTo As New Workbook
Dim WkBkFinal As New Workbook
Dim WkSheet As Worksheet
Dim CurRow As Integer
Dim FromFileName As String
Dim EndInt As Long
Dim FullRange As String

FromFileName = InputBox("Input Weekly Status File To Load", "Input File name", "C:\Documents and Settings\testname\My Documents\Company\Strategy\Dept\Weekly Status Reports\Weekly Status Report 031309.xls")
If FromFileName <> "" Then

Application.ScreenUpdating = False

Set WkBkTo = Application.ActiveWorkbook
Set WkBkFinal = Application.Workbooks.Add

'On Error Resume Next
Set WkBkFrom = Application.Workbooks.Open(FromFileName)

If Err.Number <> 0 Then
MsgBox "Error Loading File " & FromFileName
Else

CurRow = 1

WkBkFrom.Worksheets(1).Select

frmStatus.lblProcess.Caption = "Transfering Worksheets.."
frmStatus.lblStatus.Caption = "No Items Processed.."
frmStatus.Show

For Each WkSheet In WkBkFrom.Worksheets
CopyWorkSheet WkSheet, WkBkFinal, WkBkFrom, CurRow
Next

'Now lets loop through the final worksheet and copy this data to word.
CreateWordDoc CurRow, WkBkFinal

WkBkFrom.Close False
WkBkFinal.Close False

'Application.Quit
frmStatus.Hide

End If

End If
End Sub
Public Sub CreateWordDoc(CurRow, WkBk As Workbook)
Dim WrdApp As New Word.Application
Dim WrdDoc As Word.Document
Dim HdrTable As Table
Dim FtrTable As Table
Dim HtmlStr As String
Dim DtlTable As Table
Dim WrdRange As Word.Range
Dim WrdSel As Word.Selection
Dim CurRange As String
Dim CurStr As String
Dim OnFunctionColorIndex As Long
Dim OnScheduleColorIndex As Long
Dim OnBudgetColorIndex As Long
Dim NoSuprisesColorIndex As Long
Dim CurFnt As Font
Dim Lead As String
Dim Sponsor As String
Dim RecentAcc As String
Dim Description As String
Dim ToDo As String
Dim Issues As String
Dim CurTableRow As Integer
Dim StatusColumnsSet As Boolean
Dim Governance As String
Dim Approvals As String
Dim PPOStatus As String
Dim Alignment As String
Dim CurCol As Column
Dim RadioObj As InlineShape
Dim X As Integer

frmStatus.lblProcess.Caption = "Creating Word Document "
frmStatus.lblStatus.Caption = "Opening Microsoft Word"
frmStatus.Repaint

Set WrdDoc = WrdApp.Documents.Add

With WrdDoc.Styles(wdStyleNormal).Font
If .NameFarEast = .NameAscii Then
.NameAscii = ""
End If
.NameFarEast = ""
End With
With WrdDoc.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientPortrait
.TopMargin = InchesToPoints(1)
.BottomMargin = InchesToPoints(1)
.LeftMargin = InchesToPoints(0.25)
.RightMargin = InchesToPoints(0.25)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(8.5)
.PageHeight = InchesToPoints(11)
.FirstPageTray = wdPrinterDefaultBin
.OtherPagesTray = wdPrinterDefaultBin
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
.TwoPagesOnOne = False
.BookFoldPrinting = False
.BookFoldRevPrinting = False
.BookFoldPrintingSheets = 1
.GutterPos = wdGutterPosLeft
End With

SetupHeader WrdApp

CurRow = CurRow - 1

StatusColumnsSet = False

For X = 1 To CurRow

frmStatus.lblProcess.Caption = "Building Word Document "
frmStatus.lblStatus.Caption = "Processing item " & Format(X, "###,###,##0") & " of " & Format(CurRow, "###,###,##0")
frmStatus.Repaint

WrdApp.Selection.EndKey Unit:=wdStory
WrdApp.Selection.TypeParagraph

'Create the header table
Set HdrTable = WrdDoc.Tables.Add(WrdApp.Selection.Range, 1, 2, wdWord9TableBehavior, wdAutoFitFixed)

HdrTable.Columns(1).SetWidth ColumnWidth:=233.4, RulerStyle:=wdAdjustNone
HdrTable.Columns(2).SetWidth ColumnWidth:=354, RulerStyle:=wdAdjustNone

If X = 1 Then
With HdrTable
SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Project"
WrdApp.Selection.MoveRight Unit:=wdCell
SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Project Indicators/Status"
WrdApp.Selection.MoveRight Unit:=wdCell
SetCellBG WrdApp, False
WrdApp.Selection.MoveRight Unit:=wdCell
SetCellBG WrdApp, False
WrdApp.Selection.MoveRight Unit:=wdCell
SetCellBG WrdApp, False
End With
CurTableRow = 3
Else
WrdApp.Selection.MoveUp Unit:=wdLine, Count:=1
WrdApp.Selection.Delete Unit:=wdCharacter, Count:=1
CurTableRow = 1
End If

' If X = 2 Then
' HdrTable.Rows(CurTableRow).Range.Select
' WrdApp.Selection.InsertRowsBelow 1
' CurTableRow = CurTableRow + 1
' End If

HdrTable.Rows(CurTableRow).Range.Select

WkBk.Activate
CurRange = "A" & X & ".." & "A" & X
Range(CurRange).Select
CurStr = Application.ActiveCell.Value

'Calculate the colors based on the cell information.
CurRange = "E" & X & ".." & "E" & X
Range(CurRange).Select

'On Function
Set CurFnt = ActiveCell.Characters(Start:=3, Length:=1).Font
OnBudgetColorIndex = GetFontIndex(CurFnt)

'On Schedule
Set CurFnt = ActiveCell.Characters(Start:=14, Length:=1).Font
OnScheduleColorIndex = GetFontIndex(CurFnt)

'On Budget
Set CurFnt = ActiveCell.Characters(Start:=25, Length:=1).Font
OnFunctionColorIndex = GetFontIndex(CurFnt)

'No Suprises
Set CurFnt = ActiveCell.Characters(Start:=35, Length:=1).Font
NoSuprisesColorIndex = GetFontIndex(CurFnt)

'Lead
CurRange = "C" & X & ".." & "C" & X
Range(CurRange).Select

Lead = ActiveCell.Value

'Sponsor
CurRange = "B" & X & ".." & "B" & X
Range(CurRange).Select

Sponsor = ActiveCell.Value

'Governance
CurRange = "f" & X & ".." & "f" & X
Range(CurRange).Select
Governance = ActiveCell.Value

'Approvals
CurRange = "g" & X & ".." & "g" & X
Range(CurRange).Select
Approvals = ActiveCell.Value

'PPO Status
CurRange = "h" & X & ".." & "h" & X
Range(CurRange).Select
PPOStatus = ActiveCell.Value

'Alignment
CurRange = "i" & X & ".." & "i" & X
Range(CurRange).Select
Alignment = ActiveCell.Value

'Recent Accomplishments
CurRange = "o" & X & ".." & "o" & X
Range(CurRange).Select
RecentAcc = ActiveCell.Value

'To Do
CurRange = "p" & X & ".." & "p" & X
Range(CurRange).Select
ToDo = ActiveCell.Value

'Issues
CurRange = "q" & X & ".." & "q" & X
Range(CurRange).Select
Issues = ActiveCell.Value

'Scope Summary
CurRange = "N" & X & ".." & "N" & X
Range(CurRange).Select
Description = ActiveCell.Value

With WrdApp.Selection
.Range.ListFormat.RemoveNumbers
.Rows.HeightRule = wdRowHeightAuto
.Rows.Height = InchesToPoints(0)

SetCellBG WrdApp, False

WrdApp.Selection.TypeText Text:=CurStr
WrdApp.Selection.MoveRight Unit:=wdCell

'Create the Status Dtl
Set DtlTable = WrdDoc.Tables.Add(WrdApp.Selection.Range, 1, 4, wdWord9TableBehavior, wdAutoFitFixed)

DtlTable.Select

WrdApp.Selection.Font.Size = 12
WrdApp.Selection.Font.Bold = True

With DtlTable

With WrdApp.Selection.Shading
.Texture = wdTextureNone
.ForegroundPatternColor = wdColorAutomatic
.BackgroundPatternColor = wdColorAutomatic
End With

WrdApp.Selection.TypeText Text:="On Function"
WrdApp.Selection.Shading.BackgroundPatternColor = OnBudgetColorIndex
WrdApp.Selection.MoveRight Unit:=wdCell
WrdApp.Selection.Range.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
WrdApp.Selection.TypeText Text:="On Schedule"
WrdApp.Selection.Shading.BackgroundPatternColor = OnScheduleColorIndex
WrdApp.Selection.MoveRight Unit:=wdCell
WrdApp.Selection.Range.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
WrdApp.Selection.TypeText Text:="On Budget"
WrdApp.Selection.Shading.BackgroundPatternColor = OnFunctionColorIndex
WrdApp.Selection.MoveRight Unit:=wdCell
WrdApp.Selection.Range.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
WrdApp.Selection.TypeText Text:="No Surprises"
WrdApp.Selection.Shading.BackgroundPatternColor = NoSuprisesColorIndex

If StatusColumnsSet = False Then
WrdApp.Selection.Tables(1).Columns(1).SetWidth ColumnWidth:=90, RulerStyle:=wdAdjustNone
WrdApp.Selection.Tables(1).Columns(2).SetWidth ColumnWidth:=84, RulerStyle:=wdAdjustNone
WrdApp.Selection.Tables(1).Columns(3).SetWidth ColumnWidth:=84, RulerStyle:=wdAdjustNone
WrdApp.Selection.Tables(1).Columns(4).SetWidth ColumnWidth:=84, RulerStyle:=wdAdjustNone
StatusColumnsSet = True
End If

End With

End With

HdrTable.Rows(CurTableRow).Range.Select
With WrdApp.Selection
.InsertRowsBelow 1
'.Rows.HeightRule = wdRowHeightAtLeast
'.Rows.Height = InchesToPoints(1.2)

CurTableRow = CurTableRow + 1

.Font.Size = 10
.Font.Bold = True
.TypeText Text:="Scope Summary:"
.TypeParagraph
.Font.Size = 10
.Font.Bold = False
.TypeText Text:=Description
.TypeParagraph
.TypeParagraph
.Font.Size = 10
.Font.Bold = True
.TypeText Text:="Sponsor:"
.TypeParagraph
.Font.Size = 10
.Font.Bold = False
.TypeText Text:=Sponsor
.TypeParagraph
.TypeParagraph
.Font.Size = 10
.Font.Bold = True
.TypeText Text:="Lead:"
.Font.Size = 10
.Font.Bold = False
.TypeParagraph
.TypeText Text:=Lead
.TypeParagraph
.MoveRight Unit:=wdCell

SetCellBG WrdApp, False

.Font.Size = 10
.Font.Bold = True
.TypeText Text:="Recent Accomplishments:"
.TypeParagraph
.Font.Size = 10
.Font.Bold = False
.Range.ListFormat.ApplyBulletDefault
.TypeText Text:=RecentAcc
.TypeParagraph
.Range.ListFormat.RemoveNumbers
.TypeParagraph

.Font.Size = 10
.Font.Bold = True
.TypeText Text:="To Do (Next 30 days):"
.TypeParagraph
.Font.Size = 10
.Font.Bold = False
.Range.ListFormat.ApplyBulletDefault
.TypeText Text:=ToDo
.TypeParagraph
.Range.ListFormat.RemoveNumbers
.TypeParagraph

.Font.Size = 10
.Font.Bold = True
.TypeText Text:="Issues and Concerns:"
.TypeParagraph
.Font.Size = 10
.Font.Bold = False
.Range.ListFormat.ApplyBulletDefault
.TypeText Text:=Issues
.TypeParagraph
.Range.ListFormat.RemoveNumbers

End With
WrdApp.Selection.EndKey Unit:=wdStory
WrdApp.Selection.TypeParagraph

Set FtrTable = WrdDoc.Tables.Add(WrdApp.Selection.Range, 1, 4, wdWord9TableBehavior, wdAutoFitFixed)

FtrTable.Columns(1).SetWidth ColumnWidth:=131.4, RulerStyle:=wdAdjustNone
FtrTable.Columns(2).SetWidth ColumnWidth:=138, RulerStyle:=wdAdjustNone
FtrTable.Columns(3).SetWidth ColumnWidth:=141.75, RulerStyle:=wdAdjustNone
FtrTable.Columns(4).SetWidth ColumnWidth:=176.25, RulerStyle:=wdAdjustNone

WrdApp.Selection.MoveUp Unit:=wdLine, Count:=1
WrdApp.Selection.Delete Unit:=wdCharacter, Count:=1

AddControls WrdApp, X
Next
WrdApp.Visible = True
WrdApp.Selection.HomeKey Unit:=wdStory, Extend:=wdMove
End Sub
Public Sub SetupHeader(WrdApp As Word.Application)
If WrdApp.ActiveWindow.View.SplitSpecial <> wdPaneNone Then
WrdApp.ActiveWindow.Panes(2).Close
End If

If WrdApp.ActiveWindow.ActivePane.View.Type = wdNormalView Or WrdApp.ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
WrdApp.ActiveWindow.ActivePane.View.Type = wdPrintView
End If
WrdApp.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
WrdApp.Selection.Font.Size = 12
WrdApp.Selection.Font.Bold = wdToggle
WrdApp.Selection.TypeText Text:="Global Security Operations" & vbCrLf & "Weekly Project Status Report: " & Format(Now, "mm/dd/yyyy") & vbCrLf
WrdApp.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
End Sub
Public Function GetFontIndex(CurFnt As Font) As Long
'35,4,50,43,14,10 = Green
'3,46,9,53 = Red
'36,6,44,40,45 = Yellow

'Dim WrdApp As Word.Application
'WrdApp.Selection.Shading.BackgroundPatternColor = wdcolorlig
Select Case CurFnt.ColorIndex

Case Is = 36, 6, 44, 40, 45
GetFontIndex = wdColorYellow
Case Is = 3, 46, 9, 53
GetFontIndex = wdColorRed
Case Is = 10, 35, 4, 50, 43, 14
GetFontIndex = wdColorGreen
Case Else
GetFontIndex = wdColorGreen
End Select

End Function
Public Function SetCellBG(WrdApp As Word.Application, IsHeader As Boolean)
If IsHeader = True Then
WrdApp.Selection.Font.Size = 12
WrdApp.Selection.Font.Bold = True

With WrdApp.Selection.Shading
.Texture = wdTextureNone
.ForegroundPatternColor = wdColorAutomatic
.BackgroundPatternColor = wdColorGray05
End With

Else
WrdApp.Selection.Font.Size = 10
WrdApp.Selection.Font.Bold = True

With WrdApp.Selection.Shading
.Texture = wdTextureNone
.ForegroundPatternColor = wdColorAutomatic
.BackgroundPatternColor = wdColorAutomatic
End With
End If

End Function

Private Sub Workbook_Open()
End Sub

RELEVANCY SCORE 200
Preferred Solution: Excel Macro - converting excel to word doc - stops after row 29

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel Macro - converting excel to word doc - stops after row 29

Read other 13 answers
RELEVANCY SCORE 101.2

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 94.4

Hi,

I need to write a .vbs script that will start Excel, open a workbook, make some changes to the specified worksheet, and then close the workbook and Excel. The .vbs script will be called from inside a SAS program.

I have this much of it working. I call the script from a SAS program with the line:

X "T:\Programs\Format_WS.vbs
/DestFile:test.xls
/DestPath:T:\Results
/DestSheet:Sales2010
";

The .vbs script successfully starts Excel, opens the workbook T:\Results\test.xls and then saves it to another worksheet called T:\Results\newname.xls. Here is the working code:

' Read arguments passed from calling program
Set colNamedArguments = WScript.Arguments.Named
strDestFile = colNamedArguments.Item("DestFile")
strDestPath = colNamedArguments.Item("DestPath")
strDestSheet = colNamedArguments.Item("DestSheet")

'Open Excel
Set objXL = CreateObject("Excel.Application")

'Make Excel visible
objXL.Visible=True

'Open workbook
strPathFile = strDestPath & "\" & StrDestFile
Set objWb = objXL.Workbooks.Open(strPathfile)

'THE NEXT 2 LINES COMMENTED OUT BECAUSE THEY DON'T WORK
'Sheets(strDestSheet).Select
'Sheets("Sales2010").Select

'Save workbook to new file
objXL.ActiveWorkbook.SaveAs "T:\SASUsers\barnesl\Projects\newname.xls"

'Close Excel
objXL.ActiveWorkbook.Close
objXL.Application.Quit

'msgbox "script completed"

My problem... Read more

A:Problem converting Excel macro to .vbs script

Hello Lisa,

welcome to TSG Forums

You don't have to activate the workbook before referencing sheets. But you do must activate it before selecting sheets, because you can only select some part of the active workbook, active worksheet, etc. Also, in most cases you must use the workbook qualifier (objWb, in your case), which is a good practive anyway. Try this code:

Code:
objWb.Activate
objWb.Worksheets(strDestSheet).Select

Changing a cell should work without activation:

Code:
objWb.Worksheets(strDestSheet).Range("A1") = 1
This latter should work even with Excel being invisible.

JImmy
 

Read other 2 answers
RELEVANCY SCORE 94.4

I have a lotus file and at the end of this month the company are moving over from lotus to excel so i need to convert this file, the file is easier enough to do it is the macros that i am having trouble with.

TIA
 

A:help needed converting lotus macro to excel

Read other 16 answers
RELEVANCY SCORE 92.4

Hallo again! How are you keeping?

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!
 

A:Excel 2003 macro code converting comma delimiters

Read other 9 answers
RELEVANCY SCORE 92

My macro stops running and there is no error msg. My macro basically copies formulas contained in 5 cells from one spreadsheet to another but the macro just stops running after it pastes the data. No error msg. I've tried running the macro step by step and it stops running at the exact same place. If I either run the macro again or keep pressing F8, the macro begins again from the start of the sub procedure and runs completely (doesn't stop). I don't understand why it's stopping the first time I run the macro.

Here is the code:
Code:

Windows("Fund Dilution Macro.xls").Activate
Range("O5:S5").Select
Selection.Copy
Windows("NAVDILAC.USR").Activate
Range("O5").Select
[B][SIZE=4][COLOR=red]ActiveSheet.Paste[/COLOR][/SIZE][/B]
Application.CutCopyMode = False
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste

After the macro pastes the data from the first spreadsheet, it just stops running. When I am running through the macro step by step (using F8), the yellow right arrow that you usually see on the left hand side of the row of code that is running just disappears after the "ActiveSheet.Paste" line (highlighted in red above).
 

A:Excel Macro Stops Running

Hi, wlecome to the forum,
I imagine this is not all of the macro but there are no further actions here and I can't see what else follows
What is Naddilac.usr? well not much to say with this info.
 

Read other 1 answers
RELEVANCY SCORE 90.8

Hello everyone.I'm trying to run a macro which, simply put, asks a web address for some coordinates. I recorded de basic steps, due to my lack of knowledge in VBA: obtain data from a web query, select the data I want (coordinates), copy & paste the values from the query, modify the query, redo. After that i tried to adapt it a little bit, the run it. The code is here:Sub coordenadas()
'
' coordenadas Macro
'

'
Application.ScreenUpdating = False

' Create query'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://maps.google.com/maps/geo?q=" & ActiveSheet.Range("H2") & ", Santiago, RM&output=csv&sensor=false&gl=cl&key=ABQIAAAALsSOLT7cbh7X-JlSENAAQRQxTqHrN2AcRFuPjSEXWFFzkWy7kxRE5W3I6Fj83RAZ-KMoNNW2fBM03A" _
, Destination:=Range("$I$1"))
.Name = _
"geo?q=" & ActiveSheet.Range("H2") & ", Santiago, RM&output=csv&sensor=false&gl=cl&key=ABQIAAAALsSOLT7cbh7X-JlSENAAQRQxTqHrN2AcRFuPjSEXWFFzkWy7kxRE5W3I6Fj83RAZ-KMoNNW2fBM03A_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = ... Read more

A:Excel stops responding when executing a macro

Please perform the following, so that we can get the exact specs of your computer. This will better assist us in helping you more.Publish a Snapshot using SpeccyThe below is for those who cannot get onlinePlease take caution when attaching a text file to your post if you cannot copy/paste the link to your post, you will need to edit it to make sure that your Windows Key is not present.

Read other 8 answers
RELEVANCY SCORE 90

Applications:
Excel 2007, SP1 & MS Visual Basic

Background:
I use several macros in an Excel worksheet, each of which is designed to filter data in my AutoFiltered list based on some criteria.

Problem:
When a line of code in my macro CHANGES THE FILTERED STATE OF THE AUTOFILTERED LIST, the macro stops processing further lines of code. There is no error message generated when the macro stops.

To illustrate the logic of the problem:

SCENARIO 1
1. Macro turns on AutoFilter for the list.
2. Macro successfully applies a new criteria filter to the list. (i.e. THE AUTOFILTER'S FILTER STATE HAS CHANGED.)
3. Macro STOPS, failing to execute further lines of code in the macro.

Private Sub cmdShowLateStatus_Click()
With ActiveSheet
(1.) .Range("A15").AutoFilter
(2.) .Range("QuickViewOrderTable").AutoFilter Field:=11, Criteria1:="=y" (3.)
End With
' Scroll to top of list.
Range("P15").Select
ActiveCell.Offset(1, 6).Select
End Sub
SCENARIO 2
NOTE: Before the below macro starts, AutoFilter is already on, and the list is already filtered based on a certain criteria.
1. Macro successfully clears the filter in the list, but leaves AutoFilter on. (i.e. THE AUTOFILTER'S FILTER STATE HAS CHANGED.)
2. Macro STOPS, failing to execute further lines of code in the macro

Private Sub cmdShow_All_Orders_Click()
' If AutoFilter is ON, then clear any filters.
If ActiveSheet.AutoFilterMode Then
If ActiveSheet.FilterMode Then
(1.) Activ... Read more

A:Excel VBA macro stops after Autofilter changes filter state

All the mcrodoes is select the offeste 1 row down 6 colkumns to the right, thtŠts what you tell it to do.
It doesn'tdo anything after the check.
Is a sample possible?
 

Read other 3 answers
RELEVANCY SCORE 90

Good afternoon,

Does anyone know if there is a downloadable converter that will take a WORD document (extension .wps) on a computer running win 98 and convert it into an excel or word document on a computer running xp and office xp (extension .xls)
i have just changed jobs to a company i used to work for and due to the economic climate have not updated their computer system, and i was hoping that there was a program that i can add to my computer to allow me to read .wps and .wks documents generated by an earlier version of word, mainly to convert and store them onto a cd.

is there anything i need to look for on the free download sites?

best regards
phil
 

A:need help converting word 4 to excel etc

I don't know of a program that would do this. It seems to me the Word program that you have should determine if you will be able to use Excel with it or not. I would suggest that you request that your thread be moved to the Business Applications forum. You'll probably get more responses.
 

Read other 2 answers
RELEVANCY SCORE 89.2

Hello all,

I have been presented with a project that involves Forms that I am not able
to resolve.

Basically I have been asked to create a document template where other team
members can fill out a form that asks a series of questions, then to have
only the completed form results export to a new file.

I am aware that Word can export the form results to a text file, but when I
do this all of the questions that were on the original form are exported to
the new file.

What we are looking to achieve in the end:
If the end user is faced with 20 questions, but only 12 of the questions
pertain to the end users project, only the 12 completed form fields will
export to a new file.

I know that this should be possible with using a macro button to export the data, but that is where I need the help.

I tried searching for this exact situation, but I did not find any matches, hence the new thread.

Thank you in advance,

Bill
 

A:Macro help needed to export Word form results to a new file... Word/Excel

Read other 11 answers
RELEVANCY SCORE 88.8

I would like to know how to convert old files .wpd/.wpl to Excel or Word. O/S is XP. These files have been saved to a floppy and the old computer has died so cannot access files to resave.
I've tried changing the extension to txt, doc. dbf, xls etc. and opening up in several different applications but receive only non-text information (squares etc.).
Can someone help with this dinosaur file?
Thank you.
 

A:Converting wpd/wpl files to excel or word

what are they from word perfect - you may be able to open with word - in word - open - change the filetype to the ones you have
 

Read other 2 answers
RELEVANCY SCORE 88

Hello,

I've created a macro that gets selected information from a series of data in one excel worksheet then places it into another one within that excel file.

I now want to be able to select the data table I have just created from my macro and transfer it into a word document by using a macro or something similar.

I'm not having much luck though, the macro I had attempted had the file directory to which the new word file would be created when running the macro but I couldn't get any further than that as it would always prompt me with an error message and highlight the file path as the error, although there was nothing wrong with how it was written or its existance.
Thanks
 

A:Excel to Word Macro

Did you get this sorted yet? If not, please post you code.

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 88

I have the following Excel Macro that auto hides any line item that states the word off.

This Macro is used in a Word document I have that contains many little Excel tabels.

Instead of having to click on each one of these tables (there is 100+), is there something I can add to this Macro to auto search the document for these excel tables 1st?

Sub AutoHide()
Dim Cell As Range
For Each Cell In ActiveSheet.Range("A1:A65")
If Cell = "off" Then
Cell.EntireRow.Hidden = True
End If
Next Cell
End Sub

Thank you so much! This is going to save me a ton of time!
 

Read other answers
RELEVANCY SCORE 88

I have an Excel sheet where it shows the product code in column A and name of its picture in column B.

I want to copy the sheet into Word and insert the actual picture from director into column C.

I have found a macro that seems to do the same job in Excel (ie pick up the name of the picture in column B and insert the actual picture from directory into column C. But how do I do the same thing in Word?

Sub Picture() Dim picname As String Dim pasteAt As Integer Dim lThisRow As Long lThisRow = 2 Do While (Cells(lThisRow, 2) <> "") 'Range("A6").Select 'This is where picture will be insertedpasteAt = lThisRow
Cells(pasteAt, 1).Select 'This is where picture will be inserted 'Dim picname As String 'picname = Range("B6") 'This is the picture name picname = Cells(lThisRow, 2) 'This is the picture name ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored ''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' This resizes the picture ''''''''''''''... Read more

A:Does This Need Excel Or Word Macro?

Hi, welcome to the forum, I would suggest Word
Another suggestion, don't just paste your code as you did, it's unreadable.

You start with [ code ] no spaces

copy the code

and end with [ /code ]

The result is
Code:


copy the code

 

Read other 1 answers
RELEVANCY SCORE 87.2

Can you write a macro that will copy something from excel and paste it into word?

If so how do you do it? Do you write the macro in Excel or Word?

Many thanks
 

A:Macro to work in Word and Excel?

Yes, this can be done. Describe in detail exactly what you are trying to do and I'll write you a sample macro. Where should the values appear in the Word document? Which cell values will you be using? If you could .ZIP and attach some sample files that would make things much easier.

The basic steps are:

1) Set reference to Word library in your VBA Editor in Excel
2) Create a new instance of Word Application within your Excel project.
3) Execute your code on the instance of Word application.
Code:
Dim wdApp As Word.Application

Set wdApp = New Word.Application

wdApp.Visible = True

wdApp.Documents.Open ("C:\Documents and Settings\DocumentName.doc")

'ADD MORE CODE HERE

WdApp.Quit
Rollin
 

Read other 2 answers
RELEVANCY SCORE 87.2

Hello Everyone,

In cell C1, I want the user to type in any word (in this example I typed "desk") and then I have a Search Word attached to the macro:

------
Sub WordSearch()
Cells.Find(What:=Range("c1"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate

End Sub
------

My question is I want the macro to just Search in Column C and D. Not Column B or the whole sheet.

I have attached a small version of the document that has the macro.

Any input would be greatly appreciated.

TAS
 

Read other answers
RELEVANCY SCORE 86

I have an Excel macro that creates a Word document and writes to Word. I wanted to control the page breaks so I have a loop that tests whether I can fit the next chunk of text on the same page and if not, I insert a page break. Here's the strange part. If I add a message box into the loop that says "Hi", I get the proper outcome. If I comment out the message box, I get too many page breaks. That is the only change I make. Have you ever had a message box change the results of the macro? Here's the troublesome part of the code:

If wdApp.Selection.Information(wdFirstCharacterLineNumber) + charSize > 52 Then
Call insertPageBreak(wdApp, myString)
MsgBox "hi"
End If
-----------------
Sub insertPageBreak(wordDoc As Word.Application, myString As String)

With wordDoc.Selection
.InsertBreak
With .ParagraphFormat
.RightIndent = InchesToPoints(-0.5)
.Alignment = wdAlignParagraphRight
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
.TypeText (points)
.TypeParagraph
With .ParagraphFormat
.RightIndent = InchesToPoints(0)
.Alignment = wdAlignParagraphLeft
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
End With
End Sub

Read other answers
RELEVANCY SCORE 86

Hello guys!
I have a problem, and although I thought the solution was simple, it turned out to be not at al.

I have an excelfile, it contains multiple worksheets, (approx 20)
All these worksheets have a different layout, so the columns have a different size.
When I make a report I have to copy every single worksheet in excel and then have to paste them one by one as a picture in word. This takes a lot of time ofcourse..

So I thought let's make a macro.
I started to paste all the data of the different worksheets into one worksheet and then I could use 1 simple paste and copy into word and voila..
But this didn't worked out because the columns have a different size, so some of the text became unreadible.

Then I made a macro which copies one worksheet and then puts it into a new document in word and saves it, that worked out, but I don't know how to write the code in order to make the macro, after it has copied the first worksheet and pasted it into word, to make it copy the second worksheet and paste it into word and so on for all the worksheets

This is the code to make it copy ONE single worksheet and paste it into word,
but could someone please help me with the code so that it also copies the second worksheet and pastes it into the same word file?
Code:
Sub proWord()
Dim varDoc As Object
Set varDoc = CreateObject("Word.Application")
varDoc.Visible = True
Sheets("Rapportgegevens").Range("... Read more

A:(Macro?) Copy Paste Excel to Word

Read other 9 answers
RELEVANCY SCORE 86

Hello,

I have managed to write a macro that copies a table from excel into a new document in word, here is the code:

Sub controlword()

Dim appwd As Word.Application

Set appwd = CreateObject("word.application.9")

appwd.Visible = True

appwd.Documents.Add

Set wdApp = New Word.Application

Range("a1:j7").Copy

appwd.Selection.Paste

End Sub

I would like to make it so that the table copies into word NOT into a new document but into any open document at the location of the cursor...

Could anybody help me out with the code for this...

Thanks
 

A:Macro to copy a table from excel into word

You need to Set appwd to use the currently open Word Document not CreateObject.

Sub Controlword()

Dim Appwd As Object

Set Appwd = GetObject(, "Word.Application")

Set wdApp = Appwd.ActiveDocument

Range("a1:j7").Copy

Appwd.Selection.Paste

End Sub
I didn't test for the cursor location.
 

Read other 1 answers
RELEVANCY SCORE 86

Windows 2000
Office 2000
If I Step into Excel Macro below, I get to MS Word and it stops there. I have to go back to Excel Macro and then Step into Word Macro (ReplaceData).. it then executes correctly and rest of Excel Macro works ok as well. If I try to Run the Excel Macro it gives me an Error in Word Macro (ReplaceData). Should I be waiting for Word application to start/finish and or set the focus back to my Excel Macro? The Application.Run ("Step2_Create_GA710_Prn") is an Excel Macro and it runs ok ,but once again think I have to wait for Word Macro (ReplaceData) to complete before starting that Macro.

Thanks to all that can help.. I am a newbie and enjoy the site and other posts. Ralph

Sub Step1_Extract_Ga710_Data()
' Step1_Extract_Ga710_Data Macro
' Macro recorded 3/22/2005 by DANMIS8
'

Workbooks.Open Filename:= _
"C:\Documents and Settings\DANMIS8\My Documents\Ga710\Base.Wk4"

Range("A8").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select
Range("A8:N8", ActiveCell.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Copy

Set wordobj = CreateObject("Word.Application")
Application.ActivateMicrosoftApp xlMicrosoftWord
wordobj.Application.Run ("ReplaceData")
Application.Run ("Step2_Create_GA710_Prn")
Application.Quit

End Sub

Here is my Word Macro Below..
Sub ReplaceData()
'Kill "C:\Documents and Settings\DA... Read more

Read other answers
RELEVANCY SCORE 85.2

Hello, would you please assist me in solving this problem: I have an Excel object (Excel.Sheet.1) in MS Word document. I'm trying to write a Visual Basic macro for MS Word to change the data in cell A1 of this table. How can I access the cell? I can only activate the table for example like this: ActiveDocument.Fields(1).OLEFormat.Activate - this leads to the same result as double clicking on the table object...

I know how to access MS Word tables, but the problem here is that I have to work with an Excel table.. I suppose only an Excel macro can access it and modify it... And if I write an excel macro to do so, how can I call it from my MS word macro script? Does solving of this problem involve OLE, DDE...? Maybe I should run the Excel application to modify the table and then close it.. so far I have no success in doing that.. this has to be implemented in a single MS word macro.

Thank you!
 

A:Accessing excel worksheets in a word document (VB macro).

Read other 9 answers
RELEVANCY SCORE 85.2

Hi guys i'm hoping you can help me i'm a complete noob when it comes to macro's
 

A:How do i create a macro in Word that opens the data in Excel?

Read other 12 answers
RELEVANCY SCORE 85.2

Hi,

I have a requirement to populate a word document with excel data, and don't want to use links. In the attached files I have adapted a macro provided on www.datawright.com.au which is perfect except for one issue.

I have emailed the original author but haven't had a response so though I would try you guys.

The macro as written copies and pastes data into bookmarks, based on a reference table in excel telling word what data goes where. The problem is, if there are other bookmarks in the document, the macro pastes over those bookmarks with the last data copied.

Ideally I'd like the macro to only update those bookmarks identified in the reference table in excel. One side issue is that I'd still want to be able to delete bookmarks in word (so the total number of bookmarks in word may be less than that in excel). Any ideas?

Thanks in advance.

Ben

A:Macro excel named ranges to word bookmarks

Here is the macro code for those not wanting to download files:

Sub Populate_Fields()

Dim objExcel As Object, _
objWbk As Object, _
objDoc As Document
Dim sBookmark As String, _
sWbkName As String
Dim sRange As String, _
sSheet As String, _
sType As String
Dim BMRange As Range
Dim bmk As Bookmark
Dim i As Integer, _
j As Integer, _
k As Integer, _
bmkCount As Integer
Dim vNames()
Dim vBookmarks()
Dim dlgOpen As FileDialog
Dim bnExcel As Boolean

On Error GoTo Err_Handle

Set dlgOpen = Application.FileDialog( _
FileDialogType:=msoFileDialogOpen)
bnExcel = False
Do Until bnExcel = True
With dlgOpen
.AllowMultiSelect = True
.Show
If .SelectedItems.Count > 0 Then
sWbkName = .SelectedItems(1)
Else
MsgBox "Please select a workbook to use for processing"
End If
End With
If InStr(1, sWbkName, ".xls") > 0 Then
'proceed
bnExcel = True
Else
MsgBox "The file must be a valid Excel file. Try again please..."
End If
Loop

Application.ScreenUpdating = False

Set objDoc = ActiveDocument

'check to see that the Excel file is open. If not, open the file
'also grab the wbk name to enable switching
Set objExcel = GetObje... Read more

Read other 4 answers
RELEVANCY SCORE 85.2

Using Excel 2000, I want to write a macro that opens a specific word doc. I can manage in VB to open a blank word doc...but what is the command for a specific document?

thanks
 

A:Excel 2000: Macro to Open Specified Word Document

Sub OpenWordDoc()

Dim AppWord As Object
Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
AppWord.Documents.Open Filename:="c:\path\filename.doc"

End Sub
 

Read other 1 answers
RELEVANCY SCORE 85.2

I'd like to create a macro which creates a new MS Word document in the same directory as the excel book which copies specific data from an excel worksheet into the new document. The items contained within the cells should appear as a bulleted list on the word document, not a table.

Is this possible?
 

A:Macro to Export Excel Data to New Word Documet

I'm not a Word macro person at all..But I'm thinking (quick and dirty) you could do this with mail merge. Don't use the address part just 'insert merged' fields...Your 'cells' would have to be in the same row in excel.

There are also excel to word conversion programs(google). I have never used one. If your data was in Access a 'Report' would suit your needs. Also a script that reads the excel file and writes to file and saves as .doc might do this also.

Going to word 'for the sake of being in Word' seems counter productive but management wants what management wants.
 

Read other 1 answers
RELEVANCY SCORE 85.2

Good Morning guys,

This is my first post on Tech Support Forums!

I am not to sure how much help I can be to people here other than building gaming Rigs, so I will keep one eye on that forum to see if I can add value here.

Now, on to my question!

I have assembled an Excel spreadsheet with hundreds of lines of date. (Business Name, Address, Contact Number, Email, Website). We have a CRM system which does not allow me to enter new clients myself, so i have to complete a CRM Request form for each potential client on this list (over 600) The CRM request form is a Word Document with sections for each part of the data on excell.

The problem is, I am too lazy to copy and past all of the lines of data into individual CRM Request Forms (Word Documents)

So in short... correct me if I am wrong. I think I need to create some form of macro to do this for me. (copy data from excell, open up the word document, paste the data in the specific places, then save it, then repeat for the next line of data)

It would be greatly appreciated if you could point me in the right direction on this one people.

Kind Regards,
Anthony
 

A:Need Help: Excel data to individual word documents, Macro Maybe??

Read other 7 answers
RELEVANCY SCORE 84.4

Hi guys,

I have a question that has stumped me so far. Here is the scenario: I am dealing with a spreadsheet that has several tabs on it. In each tab, there are several word, powerpoint, and pdf documents that are embedded as objects. My task is to write a macro, that will for each document (keep in mind its an embedded object that needs to be opened), open it with the appropriate program, and save it using a pre-assigned name (the name of the column heading and the row it is in) to a predetermined folder path. This process needs to be duplicated about 200 times. I'm not sure if this is even possible, but I know enough about vba to believe that it is. I've been working on it since yesterday, and I can't seem to tie the pieces together. Can anyone help on this?

Thanks!
 

Read other answers
RELEVANCY SCORE 84.4

Okay, this maybe a tough one...

I have a list of registered attendees for a training session. I would like a button for each registrant that auto populates an Word invoice template and confirmation letter template - Conf. letter fields: first name, last name, company, address 1, address 2, city state/prov. and country. Invoice fields: first name, last name, company, address 1, address 2, city state/prov. and country. Session name, date range, cost...

I would also like to use the same list to populate a registrant booklet that is also an existing template in Word - first name, last name, company, email address and phone number. I read on another post that we could possibly do an auto save to a specified location when it populates the template as well. Then we have name tags, book labels and certificates that also need to be populated from the same list...

Anything to save some time and reduce errors resulting from human interference.

T Y I A
Bubbs
 

A:Excel macro to populate existing word documents and booklets

Why do you need a macro to do this? Have you looked into a "Mail Merge" ??

Rollin
 

Read other 1 answers
RELEVANCY SCORE 84.4

Hi,
I have posted this question with Microsoft Technet but no answer yet.
Based upon the following artikel:
http://www.microsoft.com/technet/security/bulletin/MS01-050.mspx

Because of a question in the Business Applications forum there was a vbs script to run open an excel workbook and execute a macro.
What surprised me is that it absolutely disregards the security settings and in no way asks if you wish to allow or disable macro's.
I tried if in Office 2003 (not yet with 2007) and any level I set security is works, no prompt.
The macro is unsigned and I tried it at work where security is quite strict.

Any ideas? Leak in Microsoft Office security?
 

A:Solved: Overrule macro security in Excel and Word with vbs script

K:
The date of the TechNet bulletin you cited is from the year 2001; updated 2003.

The article states: "Microsoft Knowledge Base articles Q306603, Q306604, Q306605, Q306606 discuss href="http://support.microsoft.com/directory/question.asp?sd=gn&fr=0">Microsoft these issues and will be available approximately 24 hours after the release of this bulletin. Knowledge Base articles can be found on the Microsoft Online Support web site."

See,for example: http://support.microsoft.com/kb/306603

http://support.microsoft.com/kb/306604

http://support.microsoft.com/kb/306605

http://support.microsoft.com/kb/306606

I hope this reduces your concerns.

RF123
 

Read other 2 answers
RELEVANCY SCORE 84.4

I am using Excel 2003 to create a database of hyperlinks to different paragraphs and then through the use of a macro I can create word documents. I inherited this macro from a different department and want to customize it for my needs. That being said I have no idea how to edit a macro this large and complex. What's the best way for me to post the macro and my questions, through email, message through this site, post it in this window? The macro is about 15 pages in length. Thanks.
 

A:Excel 2003 - Macro for creating word document question

First, welcome to the forum!!!

The best way for one of the Excel gurus to work on your problem would be to post your file, after you have removed any confidential data (make a copy and do the editing in it). If it is too large, zip it first (and zip is preferred to rar).
If you are unable to post the file (there seems to be a problem at times with people who don't have very many posts being able to attach files) just send me an email via my profile (include the URL link to this thread). I will respond to you and then you can send it to me, after which I will post it for you.
 

Read other 1 answers
RELEVANCY SCORE 84.4

How do you make a macro that does the following:

go to next cell down in Column D
copy contents (a single word) of that cell
select entire Column A
find first instance, in Column A, of the word that was copied from Column D, above, (the word is part of a sentence in a single cell in Column A),
underline just that word (not the whole sentence) in Column A
loop

 

A:Excel 2007 macro - locate specific word & underline it

Hi Brad, welcome to the forum

I just recorded a amcro that does that and translated it to vba code
Code:

Function ULinetxt(cVal As String, uStr As String)
Dim tp1 As Integer, tp2 As Integer
If InStr(1, LCase(cVal), LCase(uStr)) > 0 Then
tp1 = InStr(1, cVal, uStr)
tp2 = Len(uStr)
With ActiveCell.Characters(Start:=tp1, Length:=tp2).Font
.Underline = xlUnderlineStyleSingle
End With
End If
End Function

Code:


Sub test()
Call ULinetxt(ActiveCell.Value, "underline")
End Sub

You could use this function in vba macro and it's done

Select a cell with your data string and rin the test macro, replace the word "underline" with the text you want underlined and the first occurrence (if found) will be underlined, case is not matched.

Let me know it it works for you.
 

Read other 1 answers
RELEVANCY SCORE 83.6

Iím using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers
RELEVANCY SCORE 83.6

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

Read other 1 answers
RELEVANCY SCORE 83.6

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

Read other 1 answers
RELEVANCY SCORE 83.2

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

A:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

Read other 1 answers
RELEVANCY SCORE 83.2

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

A:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

Read other 3 answers
RELEVANCY SCORE 83.2

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

Read other 1 answers
RELEVANCY SCORE 82.4

I am working in Excel and would like to open, print, and then close an embedded Word document with a button.

Can anyone help?

Thanks for your time!
 

A:Solved: Excel Macro to Open, Print, and Close an Embedded Word Document?

Read other 16 answers
RELEVANCY SCORE 82.4

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

A:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

Read other 1 answers
RELEVANCY SCORE 82.4

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

A:Excel Macro runs fine..then excel crashes

Read other 11 answers
RELEVANCY SCORE 82.4

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

A:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

Read other 1 answers
RELEVANCY SCORE 81.2

Hi,

Recently, I'm having slow file loading while opening ms word files and excel files directly from windows explorer. It takes a minute to load, however it opens immediately if I open .doc files or .xls files directly from their application software. It also take a minute for MS Word and Excel software to shut down.

I have run my antivirus (Symantec) and MBAM but they all show no virus was found. I'm not sure what should I do next, please advice.

A:Microsoft Word & Excel - problem with opening files in Word and Excel

What version of Word & Excel?

Read other 6 answers
RELEVANCY SCORE 80.8

I have an equation that I need help with putting into an excel Macro. The equation is as follows:
=IF(AND(ISBLANK(E2),ISNA(F2)),"XXXXXX",IF(AND(ISBLANK(E2),ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNA(F2)),E2,IF(AND(OR(ISBLANK(E2)="FALSE",E2<>"000000",E2<>"111111",E2<>"999999",E2<>"IOIOIO",E2<>"XXXXXX"),OR(ISNUMBER(F2),ISNA(F2))),E2,"No Criteria Met")))))

I need this put into a macro as either an if then else statement or a case statement. If anyone could help me out i would appreciate it
 

A:Excel Equation converted to an excel macro

Not sure if this will work for you or not, untested...
Code:
dim e as range, f as range, c as range
set e = range("E2"): set f = range("F2"): set c = activecell
if e = "" and worksheetfunction.isna(f) then
c.value = "XXXXXX"
elseif e = "" and isnumeric(f.value) then
c.value = "AS SIGN LAN ID"
elseif e = "XXXXXX" and isnumeric(f.value) then
c.value = "ASSIGN LAN ID"
elseif e = "XXXXXX" and worksheetfunction.isna(f) then
c.value = e.value
elseif (e <> "" or e <> "0000 00" or e <> "111111" or e <> "999999" or e <> "IOIOIO" or e <> "XXXXXX") and (isnumeric(f) or worksheetfunction.isna(f)) then
c.value = e.value
else
c.value = "No Criteria Met"
end if
HTH
 

Read other 3 answers
RELEVANCY SCORE 80.8

Does anyone know the best way to setup a Macro in Excel 2007 that will work in Excel 2003 as well?

I have an Excel 2007 workbook that I am using a macro to change the "Fill Color" and "Font Color" of cells. However, I will get everything working in Excel 2007, but when I open the workook in Excel 2003 I get many debug errors.

If I change the code in Excel 2003, and then re-open in Excel 2007 I get debug errors.

There must be a way to setup teh workbook to work on both Excel 2007 & 2003.
 

Read other answers
RELEVANCY SCORE 80.8

A macro I wrote in VBA for Excel 2004 won't run in Excel 2011 (alas...). Upon opening the spreadsheet an "on open" routine runs, and bombs out on the following command:
Code:
Set cmdbar = Application.CommandBars.Add _
(Name:="Sort", _
Position:=msoBarTop, _
MenuBar:=False, _
temporary:=True) 'msobartop docks the menu at the top

Does anyone know if the VBA structure ("CommandBars.Add") has been removed or altered?

Is there another way to accomplish the same thing in 2011? I want to create a custom command bar (I populate it later in the module).
 

Read other answers
RELEVANCY SCORE 80.8

Excel '07 won't open an "excel 4.0" macro (i.e. *.xlm file). I am one of a few users at work using excel 2007, I believe this file was made with excel 2000 or similar. When I had excel 2000 it opened fine, it's a common file on the network that everyone should be able to access, and everyone with excel 2000 can easily. So I know it's not the file. When I try and open it with excel 2007 I get that stupid error about the file being different to the extension.

I've tried registry edits and workarounds after scowering the net but nothing seems to fix it. All it does is display the error, then opens excel in the background without even a new worksheet. Excel isn't hanging, it just does nothing. Any ideas?
 

A:excel 2007 won't open excel 4.0 macro

XLM was long ago replaced by VBA (with Excel 5 in 1993) and MS no longer really supports it (although I think they claim that they do, kinda, sorta.) So the only thing you can do, I think, is to make a copy of the xlm file and convert the xlm code in the copied file to VBA. Fortunately, I found a link that tells you how to do that:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
(Look at the link at the bottom, too. But you will need to download that to an older PC, possibly.)
Now, assuming all goes well, this will give you two versions of the same file, but if everyone else has Excel 2000 (or later) then they will all be able to use the new VBA-based file, so you won't end up with two files that would lead to confusion.
Still, it looks to be a lot of work, so good luck!

On a final note, if you need this file done in VBA and you can't figure out how to redo it, make a post describing your needs - there are some amazing VBA coders on here.
 

Read other 1 answers