Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Error 1004 When Opening VBA Excel Form With IE


Hi, all.

Can someone help me overcome the Error 1004 error I get (when opening an Excel workbook/form via an Internet Explorer link--if I open directly it works fine): Based on reading lots of great posts here, I think my problem is incomplete referencing, but I don't know what the syntax is, or where to put it (e.g. in the ThisWorkbook module, in the form's Activate sub, etc.). Heck, I don't even know how to reference a sheet right. For example, the key sheet in my workbook is Sheet2(PPIDs), but I don't know the syntax to reference the PPIDs suffix (e.g. Excel has multiple workbooks open in the same window, there might be several Sheet2s).

Maybe the easy answer is to ask how I can force my routine to highlight the desired cell in the underlying worksheet? I've tried setting Activecell=myXLSheet.Cells(X,28) but it ignores the command and active cell remains whatever was last physically clicked. Here is the code that gives me the 1004 error:

<vb>

Dim myXLSheet As Excel.Worksheet
Set myXLSheet = Sheet2
If myXLSheet.Cells(X, 28).Hyperlinks.Count = 1 Then

myXLSheet.Cells(X, 28).Hyperlinks(1).Follow NewWindow:=True

End If

</vb>




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Avoiding Error 1004 In Excel 97
code is

if Typename(Cells.Find("COmpletely Unique")) = "Range" Then
Cells.Find ("Completely Unique").Select
End if

this code prevents error 1004 in excel 2000, but it doesn't work in
excel 97, can anyone help or provide me with an excel 97
version for this code so i can also prevent the error 1004....

please guys i need some help...

Error 1004 - Macros In Excel
Hi,

I am writing a macro in Excel.
The macro copies data from one file to another.
I am using
Range("B2").select
but when I run the macro, it is stuck in that row
saying "error 1004"
if I am running it without the VB editor the error messege is just "400"
what do I do to fix that ?

Thanks
Assaf

Excel Run-Time Error 1004
Hi,

On occasions I'm getting the following error when trying to set the LeftHeader property using the PageSetup class: -


Code:
Run-time error '1004':

Unable to set the LeftHeader property of the PageSetup class


Here is the code: -


VB Code:
Sub PrintSetup(lngStartY As Long, lngPosY As Long)    '* Purpose  :   Set-up the print area and format print headers & footers.        Dim objPageBreaks As HPageBreak        Dim strLeftHeader As String    Dim strRightHeader As String        Dim strOutlet As String    Dim strTill As String    Dim strOperator As String    Dim strGroup As String        Dim lngPages As Long        With Worksheets("Price Overrides")        '* Get Header strings.        strOutlet = .lblOutletTxt.Caption        strTill = .lblTillTxt.Caption        strOperator = .lblOperatorTxt.Caption        strGroup = .lblGroupTxt.Caption                '* Format Header.        strLeftHeader = Chr(13) & Chr(13) & "&BOutlet:&B " & strOutlet & _            Chr(13) & "&BTill:&B " & strTill & _            Chr(13) & "&BOperator:&B " & strOperator & _            Chr(13) & "&BGroup:&B " & strGroup                strRightHeader = Chr(13) & Chr(13) & "&BFrom: &B" & .lblStartTxt.Caption & _            " &BTo: &B" & .lblEndTxt.Caption & " &B(Inclusive)&B" & _            Chr(13) & "&BNo. of Days: &B" & .lblDaysTxt.Caption & _            Chr(13) & Chr(13) & "&BGroup By: &B" & LTrim(.lblGroupByTxt.Caption) & " &BOrder By: &B" & LTrim(.lblOrderByTxt.Caption)                '* Setup Print Area.        .PageSetup.PrintArea = "$A$" & lngStartY & ":$K$" & lngPosY & ""                '* Ensure Page is set to Landscape and Adjust to 80% of Normal size.        .PageSetup.Orientation = xlLandscape        .PageSetup.Zoom = 80                        .PageSetup.PrintTitleRows = ActiveSheet.Rows(9).Address        .PageSetup.CenterHorizontally = True                '* Setup Print Header.        .PageSetup.CenterHeader = "&12&BPrice Overrides Report&B"        .PageSetup.LeftHeader = strLeftHeader        .PageSetup.RightHeader = strRightHeader                '* Setup Print Footer.        .PageSetup.RightFooter = "Page &P of &N"    End With  End Sub


I know what's causing the problem, it's 'strLeftHeader'.


VB Code:
.PageSetup.LeftHeader = strLeftHeader


When I reduce the detail in there it's ok, which is strange because the defualt for...


VB Code:
'* Get Header strings.        strOutlet = .lblOutletTxt.Caption        strTill = .lblTillTxt.Caption        strOperator = .lblOperatorTxt.Caption        strGroup = .lblGroupTxt.Caption


Is 'ALL'.

When the Labels are populated from my Input form and I populate Two or more of them the error occurs.

I've looked on the MS website but nothing.

What is the best work around, if there is one?

Regards

New To Excel And Getting A Runtime Error 1004
Hi I'm new to excel and am trying to create a maintenance "database" that will update the dates that maintenance is to be done by the date today. Some of the things will be done weekly/monthly/yearly. I keep getting a runtime 1004 error when I try and use:

Code:

Sub weekly_monthly_yearly()
'
' weekly_monthly_yearly Macro
' Macro recorded 10/06/2004 by Shaw Resources
'
' Keyboard Shortcut: Ctrl+d
'change dates at the start of a new week
If (Sheet1.Cells(5, E).Values Like "Weekly" And DateDiff("d", Now, _
Sheet1.Cells(5, H).Values) >= 1) Then
'if its monday
If (Weekday(Now(), vbMonday)) Then
Sheet1.Cells(E, 5).Values = Sheet1.Cells(5, E).Values + 7 'add 7 days to schedule
'if its tuesday
ElseIf (Weekday(Now(), vbTuesday)) Then
H = Now() + 1
'if its wednesday
ElseIf (Weekday(Now(), vbWednesday)) Then
H = Now()
'if its thursday
ElseIf (Weekday(Now(), vbThursday)) Then
H = Now() + 6
'if its friday
ElseIf (Weekday(Now(), vbFriday)) Then
H = Now() + 5
End If
'if its monthly maintenance
ElseIf (E = "Monthly" And Month(Now()) > Month(H)) Then
'add a month to date
H = Month(H) + 1
'if its yearly maintenance
ElseIf (E = "Yearly" And Year(Now()) > Year(H)) Then
H = H + 365
End If
End Sub


I can't seem to get by the error. I know there's a lot more I have to do like change all the E's and H's to actual data but I can;t figure out how to call on the data without the error popping up. Any help would be appreciated.

Excel,VBA, Error 1004 Problem
Hi Gang,

I am using an excel file that runs vba. The file script works the first time around then, when the parameters are changed, it used to run fine, but now it throws the 1004 error. Im not fluent in vba. I posted the file here: the file here

Please can someone have a look to see if they can find how I can fix this?

To create the error:
1) go to Main Menu
2) set model parameters
3) set a path and file name minus the .xls
4) ensure calculations in the options window are 'Manual'
OK out of set model parameters window
5) simulate
a new file is built.
6) now update the file name in the set model parameters gui
7) rerun the Simulate and CRASH
the 1004 error occurs. I have tried by deleting the contents of the sheets and not, when prompted.

Thanks in advance for any help you can offer.

Best regards,

DE

PasteSpecial Gives Error 1004 In Excel VBA
Hi, I've created custom right-click menus for an Excel project, but I still need to allow the user to paste into the cells, so I've added Paste to my right-click menus. However, I only want the user pasting values so it doesn't mess up the formatting, so clicking Paste calls the method below, which unfortunately occassionally throws an Error 1004. The most irritating part is it doesn't throw it all the time, and it seems to throw it much more in Japanese Excel (my app is bilingual English/Japanese). It works fine when pasting something from within Office, but often fails when pasting from other apps such as for example filenames from Windows Explorer. I've literally spent days searching the web and trying various things to no avail.

Code:Private Sub PasteSub()
   On Error Resume Next
   Selection.PasteSpecial Paste:=xlPasteValues
   If Err.Number = 1004 Then
      Err.Clear
      Master.PasteSpecial Format:="Text", Link:=False
   End If
   If Err.Number <> 0 Then
      MsgBox Err.Number & " " & Err.Description
      Err.Clear
   End If
   On Error GoTo 0
End Sub

I try two different pastespecial methods above just for the heck of it; seems some things don't work with the first but work with the second.

Any ideas, or possible alternative ways to approach this problem (is there anyway to duplicate part of the built-in Excel right-click menu for example?) would be really appreciated. Thanks!

Excel 2000 VBA: Getting Different Run-time Error '1004's
Hi All:

I'm going to give a lot of detail in hopes that it'll help get an answer
back; sorry for long post.

I've got a bunch of cells in workbook #2 that have formulas that combine
the values from cells within workbook # 2 *and* from corresponding cells
in workbook #1.

I have code that succesfully prompts the user for the name workbook #1
and then does a search/replace of a dummy workbook name (i.e., abc.xls)
imbedded in all the combining formulas.

Because abc.xls, the dummy workbook name, does not exist, I have leading
apostrophes on all these formulas so Excel doesn't burp by prompting for
abc.xls for each cell w/ a formula.

I also have a sub procedure to remove these apostrophes which I've used
in numerous other situations:

Code:
Public Sub ApostroRemove()
Dim currentcell As Range
For Each currentcell In Selection
If currentcell.HasFormula = False Then
'Verifies that procedure does not change the
'cell with the active formula so that it contains
'only the value.
currentcell.Formula = currentcell.Value
End If
Next
End Sub
I've narrowed down my errors to when my code is trying to remove the
apostrophes - everything else works fine.

If I run this testing code:

Code:
Sub Test_FinalStep()
' and lastly, remove all leading apostrophes
' Application.Goto Sheets("05-06 Low Income").Range("D1:E76")
' ThisWorkbook.Worksheets("05-06 Low Income").Activate
' ActiveSheet.Range("D1:E76").Select
Worksheets("05-06 Low Income").Range("D1:E76").Select
ApostroRemove
' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59")
' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate
' ActiveSheet.Range("D1:E59").Select
Worksheets("05-06 Cost Limits").Range("D1:E59").Select
ApostroRemove
End Sub

I get my 1st error '1004':

"Select method of range class failed"

and Debug points me to the "Worksheets("05-06 Cost Limits").Range
("D1:E59").Select" line.

Note: The error point to this line *if* I run the code when the "Low
Income sheets is active (visible). Also, all the apostorphes have been
removed from the "Low Income" sheet formulas.

If I run the above code w/ the "Cost Limits" sheet active (visible), the
debug points me to the "Worksheets("05-06 Low Income").Range
("D1:E76").Select" line and no apostrophes have been removed from any
formula.

Looking in newsgroups & doing misc. googles, it was suggested that
maybe something is not "active' that should be, so I've tried two
different scenarios using the above code, 1) using the
"Application.GoTo" lines and 2) using the "ThisWorkbook" and
"ActiveSheet" lines.

Each of these generated the same 2nd error '1004':

"Application-defined or object-defined error"

and debug pointed me to the "currentcell.Formula = currentcell.Value"
line at the bottom of the For Each loop in the ApostroRemove proc.

Note: Both attempts (i.e., using the Application.GoTo" and the
"ThisWorkbook/ActiveSheet") resulted exactly the same.

It also didn't matter which sheet was active when I ran the code,

In all cases all apostrophes have been removed from the "Low Income"
sheet formulas *and* also from the first cell that had an apostrophe on
the "Cost Limits" sheet.

...

thats all I can think of to try at this point ....

Anybody: any ideas/advice?

Worse case, is that I completely do away w/ the dummy workbook name in
all the imbedded formulas methodology, and after prompting the user for
the name of workbook #1, populate all the cells directly w/ constructed
string values.

I'd prefer, however, to figure this bugger out ...

Thanks in advance for any help/ideas,

Out

Run Time Error 1004 -- VB In Excel Macro
Hi, sorry to be such a rush cos I found I am in death meat within 2 more days to go before deathline, beside have a last try on here. I have a run time error 1004 while doing this... in Excel (Version 2000 (9.0.4402 SR 1) runing VB.


1) If clicked on ¡®Shift¡¯ and highlight > 1 worksheets then clicked on any cell, the Runtime Error 1004 appeared

The error is from :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'JL 19/08/02: Added for SIR-2226
If Target.Column <= 5 Then
ThisWorkbook.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
ThisWorkbook.ActiveSheet.Unprotect
End If
End Sub

However, I need this to protect the first 5 column for read only.

2) Whenever trying to save the repayment schedule onto the local desktop, the following error message is prompted and the excel will be killed off. Not able to save repayment schedule. Tested that this error message only get prompted if file is saved as the default ¡®Ms Excel 97-2000 & 5.0/95 Workbook¡¯.
If changed file type to ¡®Ms Excel Workbook¡¯ then no error is prompted.

Your expertise and lending a helping hand a much much appreciate. Any guidance and advices are welcome too. Thanks again.

Cheers,
Allan
weiloon_acc@yahoo.com

Run Time Error 1004 -- VB In Excel Macro
Hi, sorry to be such a rush cos I found I am in death meat within 2 more days to go before deathline, beside have a last try on here. I have a run time error 1004 while doing this... in Excel (Version 2000 (9.0.4402 SR 1) runing VB.


1) If clicked on ¡®Shift¡¯ and highlight > 1 worksheets then clicked on any cell, the Runtime Error 1004 appeared

The error is from :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'JL 19/08/02: Added for SIR-2226
If Target.Column <= 5 Then
ThisWorkbook.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
ThisWorkbook.ActiveSheet.Unprotect
End If
End Sub

However, I need this to protect the first 5 column for read only.

2) Whenever trying to save the repayment schedule onto the local desktop, the following error message is prompted and the excel will be killed off. Not able to save repayment schedule. Tested that this error message only get prompted if file is saved as the default ¡®Ms Excel 97-2000 & 5.0/95 Workbook¡¯.
If changed file type to ¡®Ms Excel Workbook¡¯ then no error is prompted.

Your expertise and lending a helping hand a much much appreciate. Any guidance and advices are welcome too. Thanks again.

Cheers,
Allan
weiloon_acc@yahoo.com

Error 1004 In Method Match [Excel VBA]
Code:
For n = 2 To intLRFinding
        intActRMA = oNew.Range("X" & n)
        If IsNumeric(intActRMA) Then
            strActRMA = str(intActRMA)
        Else
            strActRMA = intActRMA
        End If
        intRMA = Application.WorksheetFunction.Match(strActRMA,MyWORKSHEET.Range("A2:A" & intLRFinding), 0)
    Next n


This part generates error 1004, in the line :
intRMA = Application.WorksheetFunction.Match(strActRMA,MyWORKSHEET.Range("A2:A" & intLRFinding), 0)




Edited by - Geof on 9/6/2006 5:45:32 PM

Runtime Error 1004 For Excel Chart Creation On Second Run
Hi All,
I have an error cause by my create of chart. its runtime error 1004: Method ' Charts' of Object '_Global' failed

any clue on what its talking about ?

my code is as follows and works fine the first time my application goes through this section of the code. But the error occurs during the second time if i run this code ( ie i dun close the application; and it works during the first run)

' Add Chart
Charts.Add '' ERROR POINTS TO THIS LINE
ActiveChart.ChartType = xl3DColumnStacked
ActiveChart.SetSourceData Source:=Sheets("Chart").Range("A1:B51"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Chart!R1C1:R51C1"
ActiveChart.SeriesCollection(1).Values = "=Chart!R1C2:R51C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
ActiveChart.SeriesCollection(2).Delete

' Set Parameters of the Chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Thrust Plate Ava"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
.Elevation = 15
.Perspective = 30
.Rotation = 20
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
.HasLegend = False
End With

' Change the Background color of the Chart Area
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 50
End With

' Control the size of the Chart
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.4, msoFalse, _
msoScaleFromBottomRight

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.0000"

osheet3.Range("A1:B51").Font.ColorIndex = 2 ' set font color to white

RESOLVED: Runtime Error 1004 When Exporting To Excel
Hi, my application is getting the followed error

Runtime error 1004: Application defined or Object defined error

and this is the offending piece of code.

myBook.Worksheets(1).Range(bCol).Value = vDate

vDate is a string, and I have checked bCol as well to make sure that they have correct variables.

Any help much appreciated...

Excel, Error 1004 When Trying To Select Cell In Other Worksheet.
Hi,

I have a new problem in Excel VBA. When I try to select a cell on another worksheet it no longer recognises the object. The example below is code I have taken from running a macro but I have tried indexing the worksheets(2), naming the workbook etc with no luck. I have been told our IT Service provider doesn't support the VBA side of Excel and I will be unable to reload the software. Where the code already exists it continues to work. Not sure but the problem may have started on my first attempted at adding a CommandButton through code? (although it seems to affect all users).

Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
    Range("B4").Select
End Sub

Thank you in advance for any help you can give.

Rob.

Excel Runtime Error 1004 License Unavailable
THis is a weird one. I have been running fine on my laptop using Excel thru VB6, when all of a sudden I get this error...

"Run-time error 1004, This command is unavailable because the license to use this application has expired"
WHat the Frig is that about. On debug it lands on line
Set WkbNewbook = XlAPp.Workbooks.add

?????

Writing Data From Access To Excel - Run Time Error '1004'
I am trying to transfer data from Access database to an Excel workbook embedded in OLE box in my VB6 program. The program runs fine till it reaches a particular record in the Access database. For copying that particular record it gives "Run-time error '1004' Application defined or object defined error.

When I go to Debug mode and look for the highlighted line it always stops on that particular record. So I have deleted that record from the database and tried again. Now it gives error when it reaches the record after the deleted record.

In debug mode, if I choose the Continue option the program continues and finishes copying the data into excel.

Appended is the code


Code:
Option Explicit

Dim wkBook As Excel.Workbook
Dim wkSheet As Excel.Worksheet
Dim lRow, i As Integer
Dim rsLGR As ADODB.Recordset
Dim rsReject As ADODB.Recordset
Dim rsClass As ADODB.Recordset

Private Sub cmdDump_Click()

'copying data from Database to Excel Sheet

lRow = 4
Set rsLGR = New ADODB.Recordset
rsLGR.Open "SELECT * FROM EqpInfo WHERE WorkOrderNo = '" & rsInsp!WorkOrderNo & "' Order By ItemCode", _
db, adOpenStatic, adLockOptimistic

Set rsReject = New ADODB.Recordset
rsReject.Open "SELECT * FROM EqpTestInfo WHERE WorkOrderNo = '" & rsInsp!WorkOrderNo & _
"' Order By ItemCode", db, adOpenStatic, adLockOptimistic

Set rsClass = New ADODB.Recordset
rsClass.Open "SELECT * FROM MainItemInfo ", db, adOpenStatic, adLockOptimistic

i = 1
rsClass.MoveFirst

If rsLGR.RecordCount > 0 Then

While Not rsClass.EOF

rsLGR.MoveFirst

While Not rsLGR.EOF

If InStr(5, rsLGR("ItemCode"), rsClass("ItemCode"), vbTextCompare) Then

If Not IsNull(rsLGR!ItemCode) And Len(Trim(rsLGR!ItemCode)) > 0 Then
[color=Red]wkSheet.Cells(lRow, 1) = rsLGR!ItemCode[/color] End If

If Not IsNull(rsLGR!ItemDescription) And Len(Trim(rsLGR!ItemDescription)) > 0 Then
wkSheet.Cells(lRow, 2) = rsLGR!ItemDescription
End If

If Not IsNull(rsLGR!DetlDescription) And Len(Trim(rsLGR!DetlDescription)) > 0 Then
wkSheet.Cells(lRow, 3) = rsLGR!DetlDescription
End If

If Not IsNull(rsLGR!Degree) And Len(Trim(rsLGR!Degree)) > 0 Then
wkSheet.Cells(lRow, 4) = rsLGR!Degree
End If

If Not IsNull(rsLGR!SWL) And Len(Trim(rsLGR!SWL)) > 0 Then
wkSheet.Cells(lRow, 5) = rsLGR!SWL
End If

If Not IsNull(rsLGR!SerialNo) And Len(Trim(rsLGR!SerialNo)) > 0 Then
wkSheet.Cells(lRow, 6) = rsLGR!SerialNo
End If

If Not IsNull(rsLGR!Model) And Len(Trim(rsLGR!Model)) > 0 Then
wkSheet.Cells(lRow, 7) = rsLGR!Model
End If

If Not IsNull(rsLGR!PartNo) And Len(Trim(rsLGR!PartNo)) > 0 Then
wkSheet.Cells(lRow, 8) = rsLGR!PartNo
End If

If Not IsNull(rsLGR!PlantIDNo) And Len(Trim(rsLGR!PlantIDNo)) > 0 Then
wkSheet.Cells(lRow, 9) = rsLGR!PlantIDNo
End If

If Not IsNull(rsLGR!Location) And Len(Trim(rsLGR!Location)) > 0 Then
wkSheet.Cells(lRow, 10) = rsLGR!Location
End If

If Not IsNull(rsLGR!Manufacturer) And Len(Trim(rsLGR!Manufacturer)) > 0 Then
wkSheet.Cells(lRow, 11) = rsLGR!Manufacturer
End If

If Not IsNull(rsLGR!ManufDate) And Len(Trim(rsLGR!ManufDate)) > 0 Then
wkSheet.Cells(lRow, 12) = rsLGR!ManufDate
End If

If Not IsNull(rsLGR!ManufCertNo) And Len(Trim(rsLGR!ManufCertNo)) > 0 Then
wkSheet.Cells(lRow, 13) = rsLGR!ManufCertNo
End If

If Not IsNull(rsLGR!Status) And Len(Trim(rsLGR!Status)) > 0 Then
wkSheet.Cells(lRow, 14) = rsLGR!Status
End If

rsReject.Find ("ItemCode = '" & rsLGR!ItemCode & "'")

If Not rsReject.EOF Then

If Not IsNull(rsReject!ProofLoad1) And Len(Trim(rsReject!ProofLoad1)) > 0 Then
wkSheet.Cells(lRow, 15) = rsReject!ProofLoad1
End If

If Not IsNull(rsReject!LoadTestCertNo) And Len(Trim(rsReject!LoadTestCertNo)) > 0 Then
wkSheet.Cells(lRow, 16) = rsReject!LoadTestCertNo
End If

If Not IsNull(rsReject!Pass1) And Len(Trim(rsReject!Pass1)) > 0 Then
wkSheet.Cells(lRow, 17) = rsReject!Pass1
End If

If Not IsNull(rsReject!Defect) And Len(Trim(rsReject!Defect)) > 0 Then
wkSheet.Cells(lRow, 18) = rsReject!Defect
End If

If Not IsNull(rsReject!Comments) And Len(Trim(rsReject!Comments)) > 0 Then
wkSheet.Cells(lRow, 19) = rsReject!Comments
End If

Else

rsReject.MoveFirst

End If

i = i + 1
lRow = lRow + 1

End If

rsLGR.MoveNext

Wend

rsClass.MoveNext

Wend
MsgBox "All Items in the Lifting Gear Register are copied"
rsLGR.MoveFirst

Else

MsgBox "There are no items in the Lifting Gear Register"

End If

End Sub

Private Sub cmdSaveData_Click()

'saving data into Access datatbase


End Sub

Private Sub cmdSaveExcel_Click()

oleExcel.object.SaveAs ("C:WebLOLERLGR.xls")

End Sub

Private Sub oleExcel_GotFocus()

oleExcel.Enabled = True
oleExcel.SourceDoc = "C:WebLOLERLGR.xls"
oleExcel.Action = 0

Set wkSheet = oleExcel.object.Worksheets(1)

lRow = 1

wkSheet.Rows(lRow).Font.Bold = True
wkSheet.Rows(lRow).Font.Size = 20
wkSheet.Cells(lRow, 10).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 10) = "Lifting Gear Register"

lRow = lRow + 1

wkSheet.Rows(lRow).Font.Bold = True
wkSheet.Cells(lRow, 16).Font.Color = RGB(0, 0, 255)
wkSheet.Rows(lRow).Font.Size = 12
wkSheet.Cells(lRow, 16) = "If Load Tested"
wkSheet.Cells(lRow, 19).Font.Color = RGB(255, 0, 0)
wkSheet.Cells(lRow, 19) = "If Rejected"

lRow = lRow + 1
wkSheet.Rows(lRow).Font.Bold = True

wkSheet.Columns(1).ColumnWidth = 14
wkSheet.Cells(lRow, 1) = "Item Code"

wkSheet.Columns(2).ColumnWidth = 22
wkSheet.Cells(lRow, 2) = "Item Description"

wkSheet.Columns(3).ColumnWidth = 30
wkSheet.Cells(lRow, 3) = "Detailed Description"

wkSheet.Columns(4).ColumnWidth = 7
wkSheet.Cells(lRow, 4) = "Degree"

wkSheet.Columns(5).ColumnWidth = 12
wkSheet.Cells(lRow, 5) = "SWL"

wkSheet.Columns(6).ColumnWidth = 12
wkSheet.Cells(lRow, 6) = "Serial No."

'wkSheet.Columns(7).ColumnWidth = 14
wkSheet.Cells(lRow, 7) = "Model"

'wkSheet.Columns(8).ColumnWidth = 14
wkSheet.Cells(lRow, 8) = "Part No"

wkSheet.Columns(9).ColumnWidth = 12
wkSheet.Cells(lRow, 9) = "Plant ID No"

wkSheet.Columns(10).ColumnWidth = 20
wkSheet.Cells(lRow, 10) = "Location"

wkSheet.Columns(11).ColumnWidth = 15
wkSheet.Cells(lRow, 11) = "Manufacturer"

wkSheet.Columns(12).ColumnWidth = 18
wkSheet.Cells(lRow, 12) = "Date of Manufacture"

wkSheet.Columns(13).ColumnWidth = 20
wkSheet.Cells(lRow, 13) = "Test Certificate No"

wkSheet.Columns(14).ColumnWidth = 10
wkSheet.Cells(lRow, 14) = "Status"

wkSheet.Columns(15).ColumnWidth = 12
wkSheet.Cells(lRow, 15).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 15) = "Proof Load"

wkSheet.Columns(16).ColumnWidth = 25
wkSheet.Cells(lRow, 16).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 16) = "Load Test Certificate No"

wkSheet.Columns(17).ColumnWidth = 12
wkSheet.Cells(lRow, 17).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 17) = "Test Result"

wkSheet.Columns(18).ColumnWidth = 20
wkSheet.Cells(lRow, 18).Font.Color = RGB(255, 0, 0)
wkSheet.Cells(lRow, 18) = "Defect"

wkSheet.Columns(19).ColumnWidth = 25
wkSheet.Cells(lRow, 19).Font.Color = RGB(255, 0, 0)
wkSheet.Cells(lRow, 19) = "Inspectors Comments"


' Range("A1:C1").Select
' With Selection

' .HorizontalAlignment = xlGeneral
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = True

' End With


' With shtLGR.Shapes
'
' .AddLine(0,0,74,74)

End Sub


The Red line is giving the error.

Please help me solve the riddle.

Regards

Varrey

Run-time Error 1004 Excel 2000 Using ActiveWorkbook.Saved
I have an excel 2000 worksheet with several sub routines. The worksheets and workbook are protected. I have a pre-defined toolbar with a button that when clicked runs a macro. Everytime I click the button to run the macro it halts with "run-time error '1004' Document not saved" on the code line: ActiveWorkbook.Saved. A few weeks ago, I was working on the same workbook and it halted with this same error, but only sometimes and at one point the error didn't come up at all. But now, the error comes up every single time. I have not changed anything in the coding at all, nor have I changed any setting on my laptop. I have read several articles regarding this error and they all refer to an out of memory problem (when i right click on the code line and select Definition, I get 'Out of Memory' error prompt) because the macro is too long or you have too many applications open or you don't have the latest service pack etc. I have checked that I have all the latest microsoft updates and I have more than enough memory on my laptop. I cannot figure out why the problem is not happening consistently and hence no clue what to do to fix it. Any help will be appreciated.

Excel VB Run-Time Error 1004 Copy Method Of Worksheet Class Failed
Situation is that I've taken over support of some MS Excel macros. This example is using a template workbook and a macro workbook. A source file is read and creates in the template a worksheet for summary totals and customer detail worksheets (32) using a template worksheet with this statement:
Worksheets(5).Copy After:=Sheets(Sheets.Count)
After all the data is processed, a read-only workbook is created (.xls).
Now, I need to create additional worksheets(tabs) based on the information in the customer detail worksheets(tabs). So I've set up a subroutine to loop through and copy a new template worksheet using the statement above and then reference the customer data tab and put it in the new worksheet.
I only get about 2 worksheets created when the run-time error occurs. I've looked at the Microsoft solution, doing a Save, but since I'm working within my template (which I don't want to change, except to copy after everything is over to a normal workbook) I can't seem to figure out a way to overcome the error. There is only one SaveAs command in the macro; to create the read-only workbook at the end. This is a very large and complicated macro and I've been learning as I go (OTJ).
So any advice would be welcome. Using Excel 2003/VB 6.3

VBA Excel: Runtime 1004 Error With "Refresh BackgroundQuery" In Web Query
Anyone seen this one before? I get the notirious runtime 1004 error at the following point in an Excel macro that retrieves stock data from Yahoo:

strurl = "http://finance.yahoo.com/q?s=" & Symbol & "&d=v2"
With Worksheets("Temp").QueryTables.Add( _
       Connection:="URL;" & strurl, Destination:=Worksheets("Temp").Cells(1, 1))
           .BackgroundQuery = True
           .TablesOnlyFromHTML = False
           .Refresh BackgroundQuery:=False ' <- Bombs at this line with
           .SaveData = True ' Run-time Error '1004':
   End With ' Application-defined or object-defined error

The weird part is, it only seems to happen on my machine (XP Home, Office 2000 SR-1 SP3 as noted above), and not on a variety of others running various
flavors of Excel, from 97 to XP, on environments ranging from Win2K to Mac SoftWindows!

I put the bombing code in a blank ss with nothing else. It still bombs on the Yahoo URL (I substituted the "symbol" var with hard-coded symbol e.g. QQQ for a complete URL string). It does NOT bomb on a URL where I know the data is small and static (the Yahoo page updates dynamically before being gotten, obviously).

When I try a similar web query hand entered in a blank Excel ss using the Yahoo URL (Data -> Get External Data -> New Web Query), 4 times out of 5 I get a dialog saying:

"Unable to open <URL>. The Internet site reports that a connection was established but the data is not available."

(where <URL> is the actual Yahoo URL in the VBA at top of this thread)

I wonder if this 1004 error in the VBA macro is being caused by the query returning empty when the connection returns no data? If so, why am I getting it and no one else?? Is this a known issue?

Excel VBA: 1004 - Application-defined Or Object-defined Error
I'm getting to hate this error. I'm not doing anything I haven't done thousands of times before....


Code:

=DetermineData(Row())



Code:

Function DetermineData(ByVal Row As Long) As Boolean

Dim ws As Worksheet
Dim strDataType As String
Dim varArray As Variant
Dim varNumber As Variant
Dim rngDataType As Range
Dim rngDataLength As Range
Dim rngDataPrecision As Range
Dim rngDataScale As Range
Dim i, j As Long
Dim lngRow, lngCol As Long
Dim strTemp As String
Dim strReference As String

On Error GoTo ErrorHandler

DetermineData = False

' Set ws = ThisWorkbook.Sheets("Sheet1")
' ws.Activate

Set ws = ActiveSheet
strTemp = ws.Name
Set rngDataType = Range("DATA_TYPE")
Set rngDataLength = Range("DATA_LENGTH")
Set rngDataPrecision = Range("DATA_PRECISION")
Set rngDataScale = Range("DATA_SCALE")

strDataType = rngDataType(Row, 1).Value
strDataType = Replace(strDataType, "(", "", 1, -1, vbTextCompare)
strDataType = Replace(strDataType, ")", "", 1, -1, vbTextCompare)
strDataType = Trim(Replace(strDataType, "Byte", "", 1, -1, vbTextCompare))
varArray = Split(strDataType, " ", -1, vbTextCompare)

For i = LBound(varArray) To UBound(varArray)
Select Case i
Case 0
lngCol = rngDataType.Column
strReference = ColumnNumberToLetter(lngCol) & Row
ws.Range(strReference).Value = CStr(varArray(i))
Case 1
varNumber = Split(varArray(i), ",", -1, vbTextCompare)
lngCol = rngDataLength.Column
If LBound(varNumber) = UBound(varNumber) Then
ws.Cells(Row, lngCol).Value = CStr(varNumber(LBound(varNumber)))
Else
ws.Cells(Row, lngCol).Value = 22
lngCol = rngDataPrecision.Column
ws.Cells(Row, lngCol).Value = CStr(varNumber(LBound(varNumber)))
lngCol = rngDataScale.Column
ws.Cells(Row, lngCol).Value = CStr(varNumber(UBound(varNumber)))
End If
End Select
Next i

DetermineData = True

Exit Function
ErrorHandler:
MsgBox Err.Description
End Function


I've checked Row, it's never 0
I've tried qualifying the ranges (Set rngDataType = ws.Range("DATA_TYPE"))
I've tried using ws.Range.
I've tried using ws.Cells
I've tried using hardcoded values.
I've tried using Range().Address
I've checked the range and it's not locked. The workbook is not protected in any way.
The named ranges all exist and do not overlap.

This is getting so frustrating. Nothing works! What am I missing?

Excel 2002 (XP) on both XP and Vista - same result on both.

Error 1004: Application-defines Or Object-defined Error
Hello there,

while trying to write a script I get this error.
Here is minimal script which reproduces the error (working with Win XP and Office 2003 Sp3).

Code:
Function foo()
On Error GoTo Hell
Dim i As Integer
For i = 1 To 4
Dim r As Integer
r = Application.Caller.Row
Dim c As Integer
c = Application.Caller.Column
Dim s As Integer
s = Application.Caller.Worksheet.Index
MsgBox ("Sheets(" & s & ").Cells(" & r & ", " & c & ")")
Sheets(s).Cells(r + i, c + i) = "bar" ' this line produces the error
'Sheets(1).Cells(1,1) = "bar" produces the same error :(
Next i
foo = i
Exit Function
Hell:
MsgBox "Error-No.: " & Err.Number & vbNewLine & vbNewLine _
& "Description: " & Err.Description _
, vbCritical, "Fehler"
End Function
I tried to ask google, but I could not find any working results.
I really hope anybody here can point me to a solution.

Regards

Run Time Error '1004' General ODBC Error.
I customised a downloadable Macro in Excel sheet that is meant to connect to my Oracle database to do some charting.

Here is an excerpt of the code where the runtime error occurs in line ".Refresh" (second end line).

As I am not a programmer, this really gives me a challenge. Anyone mind to help? Thanks in advance.



strSQl = "select timestamp,mb_used,mb_free " & _
                     "from v_ora_db_space " & _
                     "where db_status='" & strDbStatus & "' order by 1"

    
     With ActiveSheet.QueryTables.Add(Connection:=strDbConnection, _
                Destination:=Range("A1"), Sql:=strSQl)
                ActiveSheet.QueryTables(1).BackgroundQuery = False
               .Refresh ' run the query
            End With

Error When Opening Excel File
Hello,
I have two problems.
1. I am trying to read an excel file from my VB.NET application. In debug mode from visual studio, I am able to open it without any problems. But when i execute the exe of my application independently, I get the exception 'File or assembly name Interop.Excel, or one its dependencies, was not found'. What am I missing here?

2.I am also not very much aware of distributing applications. Right now I just copy the exe file in objDebug and try to run it. How am I supposed to package and distribute my application? Will it be enough if I just distribute the exe file mentioned above?

Could anyone guide me on this.
Thanks in advance,
SHankar

Error Opening Excel File
I have a project that reads through a text file pulls out information and compares it to info stored in an Access DB to open an Excel file with the completed information.

The way it works is, a text file is pasted into my project and then a button is pressed to open the completed information in an Excel file.

The problem is if I try to run it twice (Run it once to open the completed info in Excel, close the Excel file and then click the button to run it through again) I get an error that says:

Run-time error '1004':

Method 'Range' of object '_Global' failed

It puts the cursor at the following line:
Code:
Range("A25:B26").Select
Which is the first line that merges and formats some cells in Excel....
Code:
Range("A25:B26).Select ' Cursor is placed here
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
obWorkBook.Worksheets(Index).Range("A" & Cell) = recSQL("bin")
If I close the project and reopen it, it works fine....

Any ideas?

Thanks a lot,

Tom

Run Time Error When Opening Excel
I'm working my way through Si's "Automating Excel Tutorial" and the early binding code is throwing a Run Time Error "Method 'Add' of objects 'Workbooks' failed". My OS is XP and I'm using Office97 with Excel8.0 from the Reference Library. It runs fine if I use On Error Resume Next but I don't like masking errors. Anyone have a clue?
Btw, the Task Mgr shows Excel running when I run the code?

Code:
Option Explicit
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook


Private Sub Command1_Click()
Set oXLApp = New Excel.Application
' On Error Resume Next
Set oXLBook = oXLApp.Workbooks.Add ' Throws error here!
oXLApp.Visible = True
Set oXLBook = Nothing
Set oXLApp = Nothing
End Sub

Error Opening An Excel Workbook
Hello,

I wrote a program in Visual Basic 6 whose only function is to automate entries on an Excel sheet. The program runs fine on my computer, but when I attempted to deploy it on a computer at work, via a deployment wizard it could not find Excel. The program came up and ran fine, but whenever I attempted to use its internal functions that open an existing Excel sheet, it crashes.

I've been attempting to make a work around for the problem, by way of converting it to an Excel Add-In, but I've never worked with addins before. When I compile it as an addin, I'm given a .DLL file, and when I attempt to open an Add-In from Excel, .DLL is not one of the supported extentions.

Any suggestions?

Error Message When Opening Excel
After reloading Microsoft Office 2003 on to a new computer with XP Platform the following message appears when 1) I open Excel and 2) when I try to close it

MICROSSOFT VISUAL BASIC

Compile Error in hidden module TBRun 9

The help feature then indicates:

There is a compilation error in the code of the specific module, but it cant be displayed because the project is protected. Unprotect the project and then run the code again to view the error.

Excel appears to work OK, but the error message is annoying. Excel worked OK with XP on previous computer. Can anyone help in correcting this error?

Jonsey

Error Opening Excel File
Hello,

I have a project which has to open an excel file. On the client machine it gives the following error:
{Project_name}has encountered a problem and needs to close.  We are sorry for the inconvenience.

This happens when with a common dialog box start to browse for an excel file and then when it is clicked the excel file it gives the error.

Client is unhappy :(

Any suggestion?
Kenguru

Opening Word Form Excel
I want to have a button which when clicked will open a new word document. Does anyone know how to do this??

Thanks

Opening A Form In Excel File
I have created a form in an Excel file and was wondering if it possible to auto open this form or open from a shortcut button

Opening An Existing Excel App In VB Form
Hi all,
I have a VB form that I would like to add an existing Excel file to as
an object, like a grid. Has anyone had the experience in doing this
type of thing?
Kevin Baker
Nuprecon, LP
35131 SE Center Street
Snoqualmie, WA 98065
(425)602-3455
email@removed

Error When Opening A Form
Hi all,

I have a program coded by VB. When I double click on the main form, it raises an error. Some thing like this:"The instruction at '0x00589ddb' referenced memory at '0x6c6584b1'. The memory could not be 'read' ".

Please let me know why? and how to fix it?

Thanks a lot.

Error Opening Form
Every time I try to load into VS a form I get the following error :

"Class.....was not a loaded control class"


Can anyone suggest to me whats up ?

TIA

Error Opening A Form
Here is the problem I am having. I have a MDI,when the user clicks a command button returning the
user to the application main form they get an error that reads:

Common Language Runtime Debugging services.
Application has generated an exception that could not be handled.

Process ID = 0x2a0(672) Thread ID = 0x6ec(1772)

When the user clicks ok the application ends.

Here is part of my problem. This only happens randomly maybe one time in 50 to 100 times on going from
one form to the main form, also I have not been able to duplicate this error in the development environment. It only
seems to happen when an exe is deployed to a user machine.

Here is the code that is being used in a form to take the user the the main form


Private Sub btnHome_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHome.Click
Dim cmdBuilder As New SqlCommandBuilder(adpRelease)
adpRelease.Update(Me.dtRelease)

Me.Close()
End Sub

Protected Overrides Sub OnClosing(ByVal e As System.ComponentModel.CancelEventArgs)
Dim frmMain As New frmMain()
frmMain.Show()
Me.Hide()
End Sub



Thanks for any help any one can give me,

Al

Error Handling When Opening An Excel Spreadsheet
I get an error 'Renamed invalid sheet name' when I open a particular downloaded spreadsheet in Excel in Windows XP. I can still open the spreadsheet manually after pressing 'Close' on the dialogue box that appears. However, I am automating a process that needs to open this spreadsheet with macros and this error stops the macro.

It has an error number in excess of 1,000 which may mean that it is not trappable.

I have tried to trap the error using 'On Error Goto XXX'. This detects the error and allows the code to continue running but the spreadsheet will not open.

Does anyone know an error handling routine that will make the spreadsheet open?

The file source cannot be changed as it is from a large organisation and the error only occurs in XP.

Could anyone help?

Automation Error Opening An Excel File
I am trying to open an excel file using the following code:

Dim ExcelApp As Excel.Application
Set ExcelApp = New Excel.Application

ExcelApp.Workbooks.Open ("C:ViaBancoPricingGrid.xls")

But I keep getting the following error:

Error #-2147417851: Automation error
The server threw an exception.

Any ideas on why it is throwing this error?

Opening A Form In Excel Automatically - This Should Be Easy!
I have a form saved in an excel sheet which I would like to appear automatically when I open the file. How can I do this?

I have tried this bit of code but it doesn't run upon opening..

Sub Workbook_Open()
UserForm1.Show
End Sub

Forgive my ignorance, I'm learning slowly...

Thanks!

Opening An Access Form From An Excel Spreadsheet
I would like to be able to open an Access form (preferably without visiblely loading the whole database in MS Access) by clicking a command button on an Excel spreadsheet.

Is this possible?

I am proficient in VB and am currently scanning the net for answers. I will stick a [resolved] in the subject line if I discover the answer.

Opening An Access Table Through Excel In A Form
Hi.

I have a form where I want to display an Access table through Excel. I need to make formulas and computations as part of data analysis and since Excel has a lot of mathematical capabilities, it was chosen to be used. My problems are the following:

1) How can I call Excel from within VB?
2) How can I display a single particular table of an Access .MDB file in the VB form?

What reference/components will I add to my VB app? I know that I have to use OLE or something to link different apps. But how can I accomplish the above questions to start with? Thanks fo the info in advance!

Runtime Error 6 On Opening Of A Form
Ok im running a fully compiled application. When I type in a command like /editchar

It opens up a form with a FileListBox, but whenever it opens it gives me a Runtime Error 6 right after.

Any idea's why this happens?

*Note for other people it doesn't happen seems to be central to my computer

Stupid Error Message (Error 1004)
OK.

I am running a macro, and I keep coming across the same error. Here is the sub that is causing the problems:


Code:
Sub FindRowsSysType()

If Application.Range("SvcLvl").Value = 4 Then
If Application.Range("SystemValue").Value = 5 Or _
Application.Range("SystemValue").Value = 6 Or _
Application.Range("SytemValue").Value = 10 Or _
Application.Range("SystemValue").Value = 12 Then
FindRowsATU
Else: PickSvc = MsgBox("The system type you have specified" & Chr(13) _
& "is not compatible with the service level you" & Chr(13) _
& "have chosen. Please choose a different service level." _
, vbOKOnly, "Please Pick A Different Service Level")
Application.Goto Reference:="PickSvcBox"
Exit Sub
End If
Else
If Application.Range("SystemValue").Value = 1 Then
strDetailType = "NoDetail"
ElseIf Application.Range("SystemValue").Value > 1 _
And Application.Range("SystemValue").Value < 6 _
Or Application.Range("SystemValue").Value > 7 _
And Application.Range("SystemValue").Value < 12 Then
strDetailType = "StanDetail"
ElseIf Application.Range("SystemValue").Value = 6 Then
strDetailType = "SprayDetail"
ElseIf Application.Range("SystemValue").Value = 12 Then
strDetailType = "DischDetail"
Else: strDetailType = "NoDetail"
End If
End If

FindRowsDetail


ReplaceSchedule

End Sub

The problem is, VBA runs through the first "If" statement just fine, but when it gets to the nested "IF" statement, it gives me this error:

Quote:




Run-time error '1004':

Method 'Range' of object '_Application' failed




How is that possible? It had just finished running through an "Application.Range" statement, so how is it getting hung up on the same type of statement? Please help.

Error 1004 Object Defined Error
VBA is the worst programming language I've ever seen ! I'm writing this macro for Exell and it keeps giving me this stupid error.
The error occures in the inner while loop, or at least that is where the debugger is reffering me to. I can't see what can be wrong. It is just a simple macro and I've written much more complecated things in C++.
Can someone help ??
Thanks in advance


Code:

Sub FindDups()
Dim current As Integer
Dim NextZip As Integer



current = NextZip = 2
NextZip = NextZip + 1




While ((current < 20717))
While (ActiveSheet.Cells([current], 3).Value = ActiveSheet.Cells([NextZip], 3).Value)
ActiveSheet.Cells([NextZip], 7).Value = 1
NextZip = NextZip + 1
Wend
current = NextZip
NextZip = NextZip + 1
Wend
End Sub

Error 1004
hi!
this is my problem: When I try to open a excel chart twice via vb the error 1004 is shown. When i open one chart it works, but i have to restart the program.

I found the source-code how to open a excel chart in a book, but it doesn't work:
(starts with an command button)
'-------------------------------------------------------------------

dim sh as worksheet
dim ex as excel.application
set wo = excel.application.workbooks.add
set sh = wo.activesheet

with sh
..."input data for the chart"
end with

ex.charts.add <- here the error is shown in the debug mode

ex.activechart.setsourcedata _
source:= ex.sheet("Tabelle1").range("A4:B7") plotby:=xcolumns

with ex.activechart
..."set the title for the chart"
end with

ex.visible=true
ex.activewindow.activate

'----------------------------------------------------------------------

i hope you can help me
benno344

Error 1004
Hi everyone. I'm getting a very annoying error message saying 'Cannot change part of a merged cell'.

Can anyone tell me what is wrong with my code?


Code:
Public strNewFileName As String
Option Explicit

Sub OldfileToNewfile()

Dim NewWB As Workbook
Dim WSName As String
Dim RngAddress As String
Dim CopyRange As Range
Dim PasteRange As Range
Dim sFilePath As String


sFilePath = "C:cassonsfamily budget calculatormy budgetsmy budget.xls"

Set NewWB = Workbooks.Open(sFilePath)

'Copy data from the 'Housing' sheet
WSName = "Housing"
Set CopyRange = NewWB.Worksheets(WSName).Range("A1:A23")
Set PasteRange = ThisWorkbook.Worksheets(WSName).Range("A1:A23")
PasteRange.Value = CopyRange.Value
Call CopyRange.Copy(Destination:=PasteRange)<<<<< bugs out here
Exit Sub

Thanks

Error Msg 1004 Help !!!!!!!!!!!!!!
I am running some large code and i am getting the following error message - 1004 - method "Range of objuect_" Global failed

the code prior to falling down is below;

xl.Range("A2").Select
xl.ActiveCell.FormulaR1C1 = "=TRIM('ORIGINAL venmast'!RC)"

this is the culprit below...........

xl.Selection.AutoFill Destination:=Range("A2:CE2"), Type:=xlFillDefault


Please help if you can, i greatly appreciate it. With this fix i should complete the application.

Error 1004 In VBE
I am developing an application in Excel using VBE where I want to call a module everytime I want to show a different WorkSheet. For example:

In a Private Sub I have set newName = "MenuSheet", where newName has been declared as Global in a module I call modGlobal. Then I call a module named chngSheet where I am attempting to set Sheets(newName).Visible = True. But I get the following 1004 Error:


Code:
Method 'Sheets' of object '_Global' failed.


When I Print newName in the Immediate window, it returns MenuSheet.

Any ideas as to what the problem is?

Cheers

Stan

Windows Error When Opening And Saving In Excel 2000
I wrote this code over winter break for my mom's work, and she called the other day to tell me this error. I have pretty well narrowed it down to when I open an excel application and spreadsheet (in one program) and saveas (in a second). I will send the code when I get a copy of it, but if this is an error somebody has seen before let me know. I worked around the saveas by using savecopy, which seems to work. It worked fine when I wrote the code, but apparently they upgraded to Office 2000, which, I've read, gets this error for various things.

The Error is:

[My Program.exe] has generated errors and will be closed by windows. You will
need to restart the program.
An error log is being created.

Please let me know if you have any quick solutions, while I try to get a hold of the code to show you. It is very annoying.

Thanks in advance for any assistance,
David

Error While Opening The Form In Design Mode
I am able to view the code generated, add my own code and run the application. But I am not able to open the form in design mode. ..and am getting error while opening the form in desig mode .. i hav uninstalled VB .. cleaned the regisrty and tried to install it again .. but still the same prob .. can any one help me?

.. iam facing a similar problem with Vs.Net also .. iam not able to open the form in design mode. But cud add code to it and build and run the app!

ThanX in advance

Run-time Error '1004'
Help!

I keep having nightmares with VBA for Excel, in particular with the above error message. It's always to do with defining my objects but I can never work out what I'm doing wrong. The hair pulled out of my head is now covering my keyboard and I am about to do serious violence to the next person in my office who coughs.

The following code is for a button embedded on a worksheet and therefore not operating through a module, (this seems to be critical though I've no idea why):

Private Sub refresh_Click()
whtMatchKey.Activate
Application.Calculation = xlCalculationManual

Dim lonLastrow As Long
Dim intLastcol As Integer

lonLastrow = whtpastedata.Range("A65000").End(xlUp).Row
intLastcol = whtpastedata.Range("AZ1").End(xlToLeft).Column

Selection.Range(Cells(2, 1), Cells(2, intLastcol)).Copy
Selection.Range(Cells(3, 1), Cells(lonLastrow, intLastcol)).PasteSpecial Paste:=xlPasteAll
Selection.Range(Cells(3, 1), Cells(lonLastrow, intLastcol)).FillDown

The second I get to the copy statement I get the runtime error. I've tried re-defining it as ThisWorkbook....etc, activating the relevant sheet earlier, activating the workbook....

Out of ideas and soon, out of hair. Any help gratefully received.

cheers

1004 Error 'Global'
Code:
Private Sub mnuAlphabetically_Click()
Dim MyXLApp As New Excel.Application
Dim strSheetName As String

MyXLApp.Workbooks.Open FileName:=App.Path & "components.xls"
MyXLApp.Worksheets("ingredients").Activate

MyXLApp.Range("A1").Select ' Code that alphabetizes the list
MyXLApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

MyXLApp.DisplayAlerts = False
MyXLApp.ActiveWorkbook.Save
MyXLApp.Workbooks.Close ' Close Excel
MyXLApp.DisplayAlerts = True
MyXLApp.Quit
Set MyXLApp = Nothing
End Sub

I get the error the second time I run this procedure. The first time it runs perfectly then the second time it has the error at the line:

Code:
MyXLApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Any ideas on what is wrong and how to fix it.

Thanks for the help.

Copyright © 2005-08 www.BigResource.com, All rights reserved