Macro Code Fails When Run On Workbook On Server

Mar 11, 2008

I have used the following piece of code to stop error checking in my excel spreadsheet:



Option Explicit

Private Sub Workbook_Open()
' Application.ErrorCheckingOptions.BackgroundChecking = False
End Sub

The problem is - works fine on my computer, but when distributed on the server the folloeing error is returned:


Run-time error '438':
Object doesn 't support this property or method

View 9 Replies


ADVERTISEMENT

Show Custom Error Message If Opening Workbook Macro Code Fails

May 17, 2008

I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?

Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True

End Sub

View 2 Replies View Related

Workbook Open Code Fails

Oct 4, 2006

I tried testing, msgbox displays the correct last modified file "MMO Activity Report 09-29-06.xls" in path D:MMOWorkfile but I was not able to open the workbook.

Sub testGetNewestFile()
MsgBox NewestFile("D:MMOWorkfile", "*.xls")
Workbooks.Open (NewestFile("D:MMOWorkfile", "*.xls"))

View 9 Replies View Related

Macro Code Fails On Other Computers/PC's

May 29, 2008

I have a workbook with macros that run on my laptop, but when I email the workbook to another user and the macro is executed I get a subscript out of range message?
Both Excel versions are the same.
Environments are the same.?

View 4 Replies View Related

Macro Fails In Shared Workbook

Sep 25, 2007

I have a workbook which I need to give shared access.

The workbook has macros which make sheets visible and VeryHidden and also give certain sheets protection. Also a filter runs on one sheet.

When I give the workbook shared access, the macros error?

how I can give me workbook shared access but still allow the macros to hide/unhide sheets, filter and protect worksheets?

View 4 Replies View Related

Data From Closed Workbook On Remote Server

Jul 4, 2007

I worked with an Ozgrid macro until it was what I wanted using files on my local PC. I then edited the macro to get data from a closed workbook on a remote server. I now have a Run-time error '1004'

Method ' Range' of object'_Worksheet' failed and the debugger takes me to this line of code.

With Sheet1.Range(AreaAddress)

The only thing I have changed up to this point in the macro, is the address of the file on the remote server.

I am at a loss with this now. Am I trying to do the impossible? ....

View 9 Replies View Related

Adding Formula Into SQL Code When Transferring Data From The Server

Jul 16, 2014

I get some tables from SQL server but I like to add formula when the data transferred to Excel sheet

"Colum C" has date data from the SQL table

I have the below formula which I like to add into SQL code which can automatically work out the aged period on "Colum D" which is named as "Aged".

=VLOOKUP(TODAY()-C2,{0,"Current";31,"31-60";61,"61-90";91,"91-120";121,"121-150";151,"151-180";181,"181-210";211,"211+"},2,1)

View 8 Replies View Related

Vba Code: Copy To Another Sheet Fails

Mar 25, 2007

Error:
Run-Time Error: 438
Object doesn't support this property or method.

Two things to note: This code was placed in the worksheet, something new I am trying and two the error line is not highlighting in yellow (as it normally does) indicating the line w/ the infraction.

Option Explicit

Sub QuickView()

Dim Wss As Worksheet 'Source Worksheet
Dim Wsd As Worksheet 'Destination Worksheet
Dim LRow As Long

Set Wss = Workbooks("TGSItemRecordMaster.xls").Worksheets(" Record Creator")
Set Wsd = Workbooks("TGSItemRecordMaster.xls").Worksheets("Quick View")

LRow = Wss. Cells(Rows.Count, "w").End(xlUp).Row - 4

Wss.Range("W3" & LRow).Copy Wsd("A3")

End Sub

[/code]

View 9 Replies View Related

Code Fails When Called From Private Sub

Aug 22, 2007

I'm having a hard time with this piece of

Private Sub Assign1Combo_Change()
If Range("ComboVisible") = False Then Exit Sub
Assign1_Download
End Sub

Symptoms:
- As soon as this is used, Excel/VBA can't select any range on the worksheet. For example, the following code (within the sub Assign1_Download) no longer works:

Range("firstdata").Select

Excel/VBA doesn't select that Named Range or any other range I try (ex. A1). - This problem only happens in Excel 2003. In Excel 2002 everything works fine (can make any selection).

View 5 Replies View Related

Chart Code With SetElement Fails In Pre 2007

Aug 22, 2008

I wrote this code so that someone else can keep track of a certain bond. It will provide him with returns and a chart between 2 periods. I wrote it on Office 2007 and it works for me, from the begining I wrote the code in an office 97-2003 compatible file. The worksheet is attached.

Sub Grafico()
Dim FechaGraficoInicial As Date 'Fecha de referencia para el rango
Dim FechaGraficoFinal As Date
Workbooks("Julius Baer.xls").Activate 'Para evitar un error, NO BORRAR
Worksheets("Precios").Activate
Columns("G:G").Select
Selection.ClearContents
Workbooks("Julius Baer.xls").Activate
Worksheets("Rentabilidades").Activate
FechaGraficoInicial = Worksheets("Rentabilidades").Range("B22")
FechaGraficoFinal = Worksheets("Rentabilidades").Range("B23")
i = 0
Condicional = 0 'Determina si el grafico se hace o no..........................

View 2 Replies View Related

Excel 2007 :: Code Fails To Put Results In Cell

Jan 23, 2013

I have the equivalent of Vlookup code(I couldn't get vlookup to work correctly) I am using Excel 2007. I have an array 2 cols x 1652 rows. I have another column of 6264 items. If a text item in the first column of the array matches a text item (minus 3 characters) in the longer column, I place the matching row from the array next to the item in the longer column. Whats not working is that the text never gets put in the cell.

Code:
Sub Macro1()
'
' Macro1 Macro

[Code]....

View 7 Replies View Related

Macro To Save In Server Folder

Sep 17, 2009

Our small company only has 5 computers in it with mine acting as the "server". I want to put a macro on our customer order forms that all computers can access on the server that will save the file as 3 or 4 cell names serparated by commas and save it in a specific location on the server.

I got the macro to work perfectly on my computer but when I try to type in the appropriate path to the server the file gets saved in their My Documents folder 99% of the time. I did get it to work twice but it seembed to be completely random as it didn't work any of the times before or after.

Here's what I have so far: ...

View 9 Replies View Related

Run Macro In File That Is Located On Server While Laptop Is Off?

Jul 25, 2014

I am looking to automate our daily sales report.

I am aware of the windows task scheduler.

However, I'd like for this report to run and be sent out very early in the morning (5AM) before I'm even at work.

My computer is usually off because it's with me as I'm commuting into the office.

These files are saved out on our network drive.

Is there a way I can "open" the file and run the macro I have that builds my sales reports with my computer being off?

View 2 Replies View Related

Excel Macro File Which Will Fetch Data From SQL Server

Sep 30, 2013

I have created a macro file which will fetch the data from sql server. Here is the code

f Me.Cmbchoose.Value = "" Then
MsgBox "Please Choose From the drop down Menu!!", vbExclamation
Exit Sub
End If
If Me.Cmbchoose.Value = "Merchants Reports" Then
Dim oConn As ADODB.Connection

[Code] .....

The code is working fine. Now my question is that I want to access it from my home. How do I do that without DSN set up?

View 5 Replies View Related

No To Overwrite Fails Macro

Jun 18, 2009

if i run my macro that saves a sheet as a certain name and then prints it, it works, but if i run my macro and the file already exists it asks me to overwrite it. If i select yes it prints it and carrys on with the rest of the macro, (good). If i select no the macro fails. How can i get it so if i hit no, it runs the rest of the macro after the print command?

View 2 Replies View Related

Macro Fails When Data Is Filtered?

Mar 14, 2014

I have a macro that sends a sheet via email when the engineer has completed it and clicks a button. I added a filter to some of the results to remove blank lines and now the macro will not work. The problem is that if I remove the filter so that the macro does work, I end up with blank lines again.

View 4 Replies View Related

If Macro Fails :: Let The Sub Carry On To Next Instruction

Nov 23, 2006

How can I let the Sub carry on to next instruction if Macro1 fails?

Private Sub Workbook_Open()

Call Macro1
' If Macro1 Fails go to next macro
Call Macro2
'If Macro2 Fails go to next macro
Call Macro3
'If Macro2 Fails go to next macro
Call Macro4

End Sub

View 9 Replies View Related

Macro To Refresh Workbook Before Next Code Run

Jul 15, 2014

I want to put a macro in that will refresh the workbook before this next part of code is run:

[Code] .....

I tried putting this in before:

[Code] ....

But it isn't refreshing the workbook before the next part is run

View 2 Replies View Related

Run Macro Code Only On Thursdays In Workbook

Jul 26, 2013

I am trying to run a Macro code only on Thursdays in a workbook.

I am having difficulty in triggering the code.

View 3 Replies View Related

Vba Code To Get Workbook Name And Enter Into Macro

Mar 9, 2009

I need vba code to retrieve the workbook name and place it into macros. the workbook name does not change in the macro if the name of the workbook changes. so i need to assign the name to a variable then use the variable name as the workbook name............

View 9 Replies View Related

Create Workbook With Macro Code Within

Jan 11, 2007

Right now I use a VBA-Code to create new worksheets in a workbook. my Question:
is it possible to attach code to the newly created Worksheet via vba-code??

View 3 Replies View Related

Open/Add New Workbook Macro Code

May 12, 2008

I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;

workbooks.add

will open a new workbook now I just need to name the new workbook based on text in my existing workbook.

View 2 Replies View Related

Save Workbook As CSV Macro Code

May 19, 2008

I want is a Macro that will save the file as .csv, with the same filename and location as the original (just in csv format). A hotkey would be quite handy for this. I tried using the macro recorder and fiddled around with the code a bit but my programming knowledge is quite limited and I ended up with something that doesn't quite work as i'd like. I'm sure this would only take a few seconds to code for an experienced user, which is why I post here. I would post my attempt at doing it myself but i'm on a computer that doesn't have excel at the moment.

View 3 Replies View Related

Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails

Feb 21, 2007

I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.
Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below

The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified)
I could comment out the debug info but the query still doesn't return data if the sheet is protected.

Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name
I apologise If my post Is difficult To follow - especially the code.

Sub AgedStockParameters()
nmb = ActiveWorkbook.Name
ndx = ActiveSheet.Name
Workbooks(nmb).Activate
Worksheets(ndx).Activate
ActiveSheet.Range("A1").Select
WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection
ActiveCell.Value = WkshtQryCon
Range("A2").Select
WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText
ActiveCell.Value = WkshtQryFld.......................

View 2 Replies View Related

Macro Code To Copy Worksheet From One Workbook To Another

Dec 30, 2011

Here is the code I have:

Windows("2.xlsm").Activate
Sheets("Report_P").Select
Sheets("Report_P").Copy After:=Workbooks("New_report.xlsx").Sheets(9)

2.xlsm is open. it contains a sheet called Report_P New_report.xlsx is open and has 9 existing sheets

Every time this tries to execute I get a "Run time error 1004 Copy Method of Worksheet Failed".

What is wrong with this code? I have an identical line in another macro, the only difference is that there are 5 sheets pre-existing, and that works fine.

View 6 Replies View Related

VB Code/Macro Printing With Page End Pulled From Workbook

Oct 29, 2008

I have a worksheet that changes in length. There is a formula that calculates how many pages the sheet will be. The also is a print button on the sheet that will print a coversheet "Sheets("Title")" and then it is supposed to print the data on the second sheet. Below the code I have so far... I know I am missing something, but knowing very little about macros I just can't seem to figure it out.

View 2 Replies View Related

Code To UNPROTECT Then PROTECT Workbook When Macro Runs

Jul 24, 2014

Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs..

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets

[Code] ......

View 9 Replies View Related

Link Cells To Chosen Workbook Macro Code

May 19, 2008

I am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.

These weekly workbooks change names depending on the week, for example, we have:

Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls

Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!

I have found out how to chose a file of my choice using:


Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

' Set up list of file filters
FInfo = "All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5 ......................

View 9 Replies View Related

Reference Data From Closed Workbook Macro Code

Jun 5, 2008

Following syntax is incorrect, what is wrong? This should happen: Select op a specific worksheet (named DATA) in the workbook Report.xls and I empty the contents

Then I try with a 'With' statement to read the contents of a file named "sourcedata.xls' with helds a worksheet named "source". The values in this sheet should be tranfered to the sheet DATA in the workbook "Report.xls"

Sub FetchDataFromClosedWorkbook()
Dim FileName As String
Dim SheetName As String
Dim cellRange As String
Const ReportPath = "C:ExcelReports"
Windows("Report.xls").Activate
Sheets("DATA").Select
Cells.Select....................

View 3 Replies View Related

Ensure Macro Code Applies To Correct Sheet & Workbook

Mar 21, 2008

I am trying to write some code that is linked to a Command button. The code in the command button is in my workbook called "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" but then I have another workbook called "MF BANK EXPOSURE SUMMARY.xls" that I want to do some work with - namely delete blank columns and it is here that I am having the problem.

My problem is this: my code module is contained in my project "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" and the columns that I want deleted are in the "MF BANK EXPOSURE SUMMARY.xls". Now even though I believe I have activated the "MF BANK EXPOSURE SUMMARY.xls" workbook the action, that of deleting the blank columns is performed on the "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" workbook. Why?? Here is the code that I am using:

Sub Commandbutton()
'ASK FOR DATE AND SET IT IN THE REPORT
Workbooks("MF Consolidated ACTUAL DAILY REPORT - Dev.xls").Worksheets _
("Seg and Non Seg Bank Summary"). Range("I1") = Application.InputBox("PLEASE ENTER REPORT DATE IN THE DD/MM/YYYY FORMAT")

'ACTIVATE MF BANK EXPOSURE SUMMARY
Workbooks("MF BANK EXPOSURE SUMMARY.xls"). Sheets("Seg and Non Seg").Activate
Dim iCol As Integer
Dim Isheet As Integer
With ActiveSheet.UsedRange................

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved