Excel 2007 :: Generate Single Sheet Workbook And Save To Sharepoint Site

Jan 4, 2012

Automating Excel from Access, I'm attempting to generate a single sheet workbook and save to a Sharepoint site. This process works fine on my machine (XL2007, XP), but on a coworkers computer (XL2003, XP), the code throws a 1004 error on the save as line. However, the really odd thing is that stepping through the code doesn't throw an error on the coworkers machine.

Here's the sub being ran; the line it errors out on is the first branch in the .saveas block.

Code:
Private Const csSharePointSaveAs = "\sharepoint-us.mycompany.comsitesfinance adminlah blahlah-blahCustomer Publication Tracking.xls"

Private Sub PublishXLtoMOSS()
Dim objXL As Excel.Application 'Object
Dim wb As Excel.workbook 'Object
Dim ws As Excel.Worksheet 'Object
Dim rs As DAO.Recordset
Dim i As Long

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

[Code] ...........

View 1 Replies


ADVERTISEMENT

Excel 2010 :: Workbook Not Closing Correctly When Accessed Via Link On Sharepoint Site

Feb 18, 2013

I was asked to create a userform for the use of our whole team which would enable them to select a name from a dropdown list and then it would display their business contact details in the rest of the form.

I'm running Office 2010 and Windows XP server/enterprise edition (not quite sure which). After some research I've managed to use VBA code to open the userform while hiding the workbook, then when the close button is clicked it closes the user form and it's associated workbook using the code below.

Code:
' ThisWorkbook module
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub
'UserForm module
Private Sub CommandButton1_Click()
Unload UserForm1
ThisWorkbook.Close (False)
End Sub

This works great when the excel file is accessed directly. However when the excel file is accessed via the link on my teams Sharepoint site it doesn't. When the Userform is closed the underlying workbook and data is then displayed which then has to be closed separately.

View 2 Replies View Related

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

Jun 25, 2014

I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.

Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")

[code]....

View 2 Replies View Related

Save A Single Sheet In A Workbook Using A Macro

Apr 2, 2009

I need to save a single sheet to a file (in a workbook of many sheets). I can't use the copy command and then save the new workbook because it truncates the longer info in some cells.

View 9 Replies View Related

Excel 2007 :: Search For A File In Sharepoint Using VBA

Apr 18, 2014

I want to search for a file in Sharepoint using Excel 2007 VBA.

Path to the Sharepoint location where the files are at is [URL] ....

File name is customer_list_xxxxxx.xlsx The x's are a date which changes every day or week. So a new file will be uploaded to the sharepoint path.

Example customer_list_041414.xls

I've tried so many different code options for this, but no luck.

View 1 Replies View Related

Excel 2007 :: SharePoint / Checking File Status

Jun 11, 2014

I'm working with a file from SharePoint (FileA), "FileA" goes out to Sharepoint and opens "FileB" (if not already open) then copies information from "FileA" to "FileB". However if "FileA" is left open, or someone else has it open I am unable to open "FileA". How in VBA can I check to ensure I don't have it open locally (if so swich and continue), checkout and open in SharePoint (if not already open) or Prompt with options to force checkin, checkout to copy info?

Simple VBA copy I used:

Workbooks.Open FileName:= _
"http://sharepoint.baaa.com/sites/SCDT_DSD/Weekly_Releases/11.%20Combined%20Weekly%20Task%20List/1/Automated_Status_Update.xlsm"
Sheets("1").Select
Range("A1").PasteSpecial Paste:=xlPasteValues

View 2 Replies View Related

Excel 2007 :: Protecting Worksheet / Workbook On Save?

Nov 24, 2011

Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?

View 9 Replies View Related

Excel 2007 :: Saving And Loading Single Sheet?

Dec 21, 2011

My set up office 2007 win xp pro ie8

is it possible using vba ideally with a button marked save, to save a single worksheet and name it, in CSV format

Then I would like to be able to do the reverse load a CSV file back in to a work sheet

Expanding upon this

1. Click a command button with caption “save”

2. Take the name of a given sheet, in my case DataToUpLoad and append it with a number to give a sheet name of DataToUpLoad 1 the first time it is save and DataToUpLoad 2 the next time and so on

3. Click another command button with the caption “load” interact with usual dialog box.

My current work around is to copy a sheet open a new work book past the sheet in the save that work book as CSV

The reason this is required is to mate up with the php on the server side

View 3 Replies View Related

Excel 2007 :: Save Files XLS In Macro Workbook And Send Email

Sep 3, 2012

I am using 2007. This may be something simple but I can't seem to find a solution to this, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file here is the code used for the tasks in the

workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub
Sub step2_save_close()

[Code] ........

View 1 Replies View Related

Excel 2007 :: Save Files XLS In Macro Workbook And Send Email?

Sep 3, 2012

I am using 2007, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file.

Here is the code used for the tasks in the:

workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'

[Code]....

View 7 Replies View Related

Excel 2007 :: VBA To Save And Close All Open Workbooks Except Macro Workbook?

Mar 8, 2013

I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.

View 8 Replies View Related

Excel 2007 :: Copy / Paste All Rows For Each Unique Name And Save In Separate Workbook

Dec 11, 2011

I am using Excel 2007

I have a spreadsheet with 1,000 rows in multiple columns

In column "B" i have 8 unique names.

What I am trying to work out is to copy and paste all the rows for each unique name and save in a separate workbook named as the unique name.

View 2 Replies View Related

Excel 2007 :: Cell Show No Value When Open Sheet On Another Computer - Value Appears When Save Again

Mar 13, 2014

when I open my sheet on another computer, all cell value which contain formula show no value. just blank. formula is link to same workbook on different tab. when I save as again on my desktop, all value appears. excel 2007 is using.

View 3 Replies View Related

Save Excel Sheet In PDF Format With The Name Of Workbook

Jun 23, 2012

I have the below macro steps to save an excel sheet in PDF format in "C:UsersxxxxxDesktop" and with the name of the workbook.

But when the PDF report gets published in c: drive , the PDF file name has .xlsm added to it instead of just the file name.
I would like to know how to publish the file in PDF format just in the name of the workbook.

Find the below macro steps:

Sub Docsave()
Dim docname As String
docname = ThisWorkbook.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub

View 1 Replies View Related

Excel 2010 :: Macro To Upload SharePoint Project List Then Copy To Existing Workbook?

Jul 11, 2014

I need to be able to export a project list to Excel and overwrite the contents in an existing Summary Report with the exported list. Is this possible? I am using SharePoint 2013 and Excel 2010.

View 4 Replies View Related

Excel 2007 :: Delete Sheet On A Shared Workbook

Oct 29, 2013

I have a file that has an original file format .xls

I saved as .xlsx (I am using Excel 2007) with a different name so I could make changes to the new file without changing the original.

That turn off compatibility mode.... but it is still "shared". While it is shared I cannot delete a sheet. In fact, I really don't even want it to be shared. If the file is open, and someone else opens it, I want it to open as "read only". I cannot uncheck the "Allow changes by more than one user at the same time. This also allows workbook merging." checkbox because it is greyed out.

View 3 Replies View Related

Generate Outlook Email From Excel List Of Recipients Via Single Click?

Jan 2, 2014

I would like to generate a blank email with a list of recipients (no more than 40) in the 'To' field. I would like to do this in a single click on a cell with the text 'newsletter group' in it (cell B17). The recipients email addresses are in the format of one complete email address per cell in a column starting at C17 (C17, C18, C19, etc...). I thought I had found a way to do this (using the hyperlink function I think, but can't quite remember)but it turned out you could only string together about 8 email address before the function stopped working.

Also just to be clear, I don't want to send the workbook to the recipients.

View 4 Replies View Related

Excel 2007 :: Use Macro To Generate A Form

Mar 11, 2012

I have a sheet with 500 rows and 20 columns. I want to put an input box and a button on a different sheet. What I would like to happen is that when you put the criteria in the input box and click the button it opens the form for the row corresponding to the criteria and allows you to edit the 20 columns. Excel 2007.

View 6 Replies View Related

Excel 2007 :: Generate Possible Combination Of 3 Series

Nov 5, 2012

I've 3 series that are

1- From 0-9
2- From 0-3, and
3- From 0-27

I want to generate possible combination of these 3 series in Excel 2007.

I think total combinations would be (10*4*28=1,120)

View 1 Replies View Related

Save A Single WorkBook Tab To A New File

Aug 28, 2008

I am using Excel 2003 and need to save via VBA code a single tab in a workbook to a new workbook. I need to be able to specify the new workbook file name and location. The copy feature on the tab won't work because the data is too large and I get an ugly message using that option.

View 9 Replies View Related

Excel 2007 :: Split Sheet Into Workbooks Using Workbook Template Based On Data In Column A

Oct 5, 2011

I have a workbook that has a sheet called CustInvData, this sheet contains 4,421 rows of invoice transaction data for 178 customers starting on row 2 (headers on row 1). I need to split the transaction data for each customer out into a workbook template based on the customer name in column A. I need each workbook named by the customer name along with a month and year (example: Bellsouth-0911.xls), this should create 178 unique workbooks. And since we sometimes have to go back and rerun invoices for previous months, I'll need to control the month and year manually in the code.

The parsed data needs to be copied to a pre-formatted invoice template. This template has 2 sheets, Sheet1 is called 'Product Summary', this is a table that uses VLOOKUP functions to read the data in Sheet2 called 'Product Details', this is the sheet the parsed data needs to be copied to for each customer invoice. The 'Product Details' sheet has formatted rows 1 thru 11, row 11 being the header row for the data from CustInvData to be copied. So the parsed data needs to start at row 12.

Last, once the data has been copied into the 'Product Details' sheet, I need the data to be SubTotaled at each change in column J (Product) and use the 'Sum' function to add a Subtotal in column L (Retail Price) for each unique product category.

Example data below, I've simplified it (the actual data array spans from columns A to Y)

Customer NameProduct Retail Price
ABC CompanyAVMPCR10
ABC CompanyAVMPCA15

[Code]....

I'm a bit of a novice with macros, but I know Excel pretty well.

Using Excel 2007 running on Windows Vista

View 2 Replies View Related

Excel 2007 :: Automatically Generate Email When Button Is Pushed

Oct 25, 2012

I am using the following piece of VBA code in Excel 2007 to automatically generate an email when a button is pushed:

Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
r = ActiveCell.Row
'Get the email address
Email = Cells(r, 3)

[Code] .....

The button to launch this code sits in cell AK7. What I would like to do is have the macro copy the email address present in cell E7 into the email address section of my created email. In addition I would like the reference provided in cell AJ7 to appear in the Message Subject part of the generated email in the format " RCS Reference contents of cell AJ7".

View 3 Replies View Related

Import Data From A Single Cell In A Table On A Site

Jul 19, 2013

We have a list of around 800 clients containing client id, name and e-mail and i need to add contract number. I can only get the contract number by accessing the admin (password-protected) using a URL that looks like this admin/index.php?client=id.

Problems:

1. if i use the From Web option in excel's data tab, i get the entire table not just the needed cell
2. if i get the data using the above mentioned option, i can only do it from one client at a time

View 5 Replies View Related

Macro (VBA) To Save File To Web Directory (SharePoint)

Jun 17, 2014

I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?

ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.

View 1 Replies View Related

Excel 2007 :: Generate Multiple Workbooks With Worksheet Data Source

Jan 17, 2012

I am looking for macro where it can generate multiple workbooks with a single worksheet data source. I have this worksheet with data which is look like this:

[IMG][/IMG]

This data will be places in 1 workbook for each of line with given file name as page number above those workbooks generated will have same information for each line but at different cell position such as: take this example at line 3

and

Microsoft Excel 2007
Window 7Pro 64bit

This line could be up to 50 lines of data. I try google search but found most about consolidate multiple workbooks to single worksheet. I do have VBA reference that I refer to from [URL] ......

How to put the information from the worksheet into specific position in the workbooks.

View 1 Replies View Related

Excel 2007 :: Generate Microsoft Word Document From Data Encoded In Spreadsheet?

Jun 18, 2012

I need to generate a microsoft word document from the data encoded in my excel spreadsheet. I am currently using MS office 2007.

in the Excel Spreadsheet from columns C to F "a) b) c) d)" was not typed but in the word document it automatically appears before the choices encoded in excel. Another thing is that some of the choices typed in the excel spreadsheet are in bold font and I want it to be generated in word document with the bold font as well.

By the way I am planning to use this technique to create a 100 item multiple choice exam for my students as I find using EXCEL to generate the document a lot easier than creating the test manually in WORD.

View 1 Replies View Related

Save To File Single Sheet Only

Mar 21, 2012

There are multiple tabs in my workbook ("Template", "Database", and "Summary"). I would like to add a "Save to file" button on the "Template" sheet and it will dump a copy of this sheet only using the data on cell C4 and C7 as filename, not the entire workbook.

View 9 Replies View Related

Excel 2003 :: Unable To Allow Users To Make Changes To Workbook But Only Be Able To Do Save As Not Save

Dec 3, 2012

I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.

View 4 Replies View Related

VBA To Save Single Sheet As Tab Delimited Text File

Jun 5, 2014

Is there any way to write a macro that will take a specific sheet in a workbook and save it as a tab-delimited text file?

View 1 Replies View Related

SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook

Jun 17, 2014

I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)

However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.

I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.

For now it is only saving the copy into "My Documents"

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'

Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

[Code] ........

View 2 Replies View Related







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