Free Microsoft Excel 2013 Quick Reference

Create a new excel workbook from access

I need VBA code for -
To open a new excel workbook from access and then insert data or create
a pivot in excel sheet.
I want to pass the name of the file so that its created in excel as
..xls file and the pivot is created in that.

Thanks a lot in advance for your help


Post your answer or comment

comments powered by Disqus
Could anyone help me with some Vba to print a closed Excel workbook from access (or open it, print it then close it again)
Thank you.

Hi

I want to create a new excel application through macro.

I basically want to transfer few columns from the parent workbook to new workbook. but these new workbook must be contained in a new excel application..

Can anybody suggest me a fast way to do it?

Regards,
Vaibhav

Hi All,

I need your help once again. I have a Dashboard with five sheets and I
was wondering if there is a function to create a new Excel file based
on the data selected from the Dashboard.

I would like to select data or cells from different sheets from the
Dashboard then create a new Excel file with the data selected using a
macro or vb.

I guess I could just copy and paste the data from one file to another
but there are a large number of cells I need to select. I would like to

know if I could automate this process.

Thanks in advance.

i used to be able to open a blank excel workbook from the beta visual studio
express but now the new one has been released it doesnt recognise excel using
the microsoft interop and wont open any suggestions
--
BD3

Hi, I never did any macro before. I need to have a macro that will
capture some specific cell/rows and save/create those info in to a new
excel file. Is that possible? Please help!

Hi All,

I need your help once again. I have a Dashboard with five sheets and I
was wondering if there is a function to create a new Excel file based
on the data selected from the Dashboard.

I would like to select data or cells from different sheets from the
Dashboard then create a new Excel file with the data selected using a
macro or vb.

I guess I could just copy and paste the data from one file to another
but there are a large number of cells I need to select. I would like to

know if I could automate this process.

Thanks in advance.

Jose

Hi All,

I need your help once again. I have a Dashboard with five sheets and I
was wondering if there is a function to create a new Excel file based
on the data selected from the Dashboard.

I would like to select data or cells from different sheets from the
Dashboard then create a new Excel file with the data selected using a
macro or vb.

I guess I could just copy and paste the data from one file to another
but there are a large number of cells I need to select. I would like to

know if I could automate this process.

Thanks in advance.

Hello ,

Is there a way of creating a new excel workbook using a macro that is stored as an Add-In.

The new workbook is to be created in the same folder. If I use the following command in macro then it saves the file in the Add-In folder.

Can somebody please help !

Thank you
arora

I am trying to work out how to create a series of workbooks from a single workbook - let me explain

I have a workbook that carries out a series of actions and then saves the workbook with a new name, leaving the "template" in it's original format.

What I want it to do is save book1 then create book2 .....bookx based on a list in the template.

Can anybody point me in the right direction?

Thanks

I am writing a plugin with which , when the user clicks a button, I
want to save the active sheet in a new excel file.

How do I create a new excel file programatically?
howo do I add a existing sheet to this file?

Regards
Chimanrao

Hi all, new to forum, wonder if anyone can be of help with a little problem. I would like to create a new Excel workbook, preferably naming worksheets at creation time, from Access using VBA. Here is a sample of code I have been trying:

Private Sub Command2_Click()
Dim xlBook As Object
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Create("C:Documents and SettingsLolDesktopTrial.xls")

End Sub

I keep getting "method not supported" error messages referring to the .Create part. i have tried .Add and .New but get nowhere. Could someone please point me in the right direction please?

Thanks, Lol

Hi All

I designed an application in Excel as a frontend and Access as a backend. Now my problem is to create a new temp table from the main table each time user press enter so that if sometimes a user makes some mess in the main table then we have the backup present and so we can recover any disaster to main table without doing anything manually.
I want vba code so that when the user press enter button then before making any changes in the main table in Access, make another table and save all the data in it and name it with current date and time. so every time the user press enter a new table should be created first before making any changes in the main table. So in total the 100 temporary tables can be created and when the count of new tables reach 100 then it should delete all of temporay table except the main table. I know we can use the following statement to create a temporary table from the main table:
But how to name each new created table with current date and time and how to write a condition that when the count reaches
100 then delete all the temporary tables.

I hope anyone can help me out.

Thanks

Hello everyone,
I have a button on userform and here is what I want it to do.
I want that when that button is pressed a browse folder appears and when user selects a suitable location from that browse folder for his workbook, a new excel workbook with the name as given by user is formed and opened where the user can perform further operations.

Hope, this is clear.
Kp

This one might be a bit complicated:

I want the ability to create a new word document from inside excel and merge in certain data from the worksheet to specified fields in the word document (address, file number, fax number etc.). Ideally I would like to be able to select one "Contact" (maybe with a check box of some sort) and then select a custom toolbar button to create a letter with that Contact's information already in the fields.

Bonus points: Instead of a new word document, open a template from a template folder then save with a different file name and the above merged data in the specified fields.

Any help would be appreciated. I am a novice with excel and VBA but am slowly figuring it out.

I have an MS Access 2000 database that uses COM automation to create a new
Excel workbook and transfers data to it and then call's the Excel workbook's
SaveAs dialog box so that the user can save the workbook in any format he
chooses (eg xls, csv etc).

When the user tries to save the workbook in the default folder specified in
the dialog box (My Documents) , everything goes smoothly. However, if he
tries to change the directory first, the dialog seems to freeze for about 15
seconds. If a button is pressed on the dialog, then a Switch to / Retry
error comes up.

I'm sure I've seen something similar before. Does anyone have a solution to
this problem?

Private Sub Btn_Export_Click()
Dim Last_Row As Long, Msg As String, Response As Integer
    If Me.Lab_Plan_ID.Caption = "New Plan" Or Me.Lst_PlanList.ListIndex = -1 Then
        MsgBox "Please select a plan to export", vbOKOnly, "No Selection"
        Exit Sub
    End If
    Msg = "Are you sure you want to export the selected plan?"
    Response = MsgBox(Msg, vbYesNo, "Plan export")
    If Response = 7 Then Exit Sub
    WS_Temp.Select
    Last_Row = WS_Temp.Cells(Rows.Count, 2).End(xlUp).Row
    
    Set DataDump = ExcelSession.Workbooks.Add
    ExcelSession.Visible = True
    ExcelSession.DisplayAlerts = False
    If DataDump.Worksheets.Count > 1 Then
        Do
            DataDump.Worksheets(DataDump.Worksheets.Count).Delete
        Loop Until DataDump.Worksheets.Count = 1
    End If
    ExcelSession.DisplayAlerts = True
    DataDump.Sheets(1).Name = "Transition Plan"
    WS_Temp.Range("A1:N" & Last_Row).Copy Destination:=DataDump.Sheets(1).Range("A1")
End Sub


The code above creates a new workbook but, doen not paste data into it. The error message is "Copy Method of Range Class Failed"

Please help!!!
Thanks,
Baapi

Hi,
i'm new to excel macros, and don't belong to programming fraternity, so right now i need to build a smart macro which can do the following:
Let us say this is how my excel sheet looks
sheet1:

1 2 3 4 5
A Aone Atwo Athree Afour Afive
B Bone Btwo Bthree Bfour Bfive
C Cone Ctwo Cthree Cfour Cfive
D Done Dtwo Dthree Dfour Dfive
E Eone Etwo Ethree Efour Efive

above are the contents of my excel sheet, consider it as a matrix.
now i want to create a new excel sheet which will contain a column with all my cell data mapped with their corresponding row column index, i.e., as shown below
Column1 Column2
Aone A1 // look above where is 'Aone', it is in row 'A' and column '1' so column 2 will contain 'A1' alongside
Atwo A2 // look above where is 'Atwo', it is in row 'A' and column '2' so column 2 will contain 'A2' alongside
Athree A3 // look above where is 'Athree', it is in row 'A' and column '3' so column 2 will contain 'A3' alongside
Afour A4 // look above where is 'Afour', it is in row 'A' and column '4' so column 2 will contain 'A4' alongside
Afive A5 // look above where is 'Afive', it is in row 'A' and column '5' so column 2 will contain 'A5' alongside
Bone B1
Btwo B2 and so on

refer the above table to understand clearly the scenario..........
Even now if u don't get it, then let me make it more clear:
I need to create a macro which should do the following:
1-- Creates a new excel sheet
2-- should transfer all the data in each cell(from the current sheet) as a single column data in the new excelsheet.

In furtherance i have attached an excelsheet which has two tables which will give you a clear picture, the table above is the current sheet( input table) and the new sheet should contain the data as described in second table (output sheet data) below .

Please guide me with your invaluable inputs.

Thanks

I used to be able to open a new excel document from the file open menu and
this would open up a new excel app with the new sheet in.
I am not sure what has changed but now when opening a new sheet it opens
within the same workbook.

I have noticed that new word documents are opening in the way described above.
How can i get Excel to do the same please,

Regards,

Osman Ismail

I have a master document that is used to store information relating to projects that have been registered that require audits. A report is run each month, and new projects are added to the excel document. The document is designed to not only store this information, but to also show which of these projects registered have been audited. The audit detail is manually keyed in once the project has been audited.

I have been trying to program the master document so that when you click on a form control only information relating to that form control is created in a new excel document (separate to the master document). For example if I click on a form control titled NSW, only information relating to NSW is picked up and created in a new document.

I have done this in a previous document, but the information was static and the parameters never changed. Eg if I clicked in a form control titled “survey” all information relating to survey was picked up. This was easy as all that information was between rows 20 and 50. The issue I have with this document is that the parameters will be always changing as the document grows in size. The document titled "example document" gives you an idea of what im looking at doing.

I have attached a document – titled sample document of what I need to do with some instructions.

Cheers …. fmluder

OK!

I am trying to make a Macro that will cycle thru the values of a range on sheet2 and create a copy of sheet3. I need a new sheet for every value in the range that ends a certain way. Each new sheet must be named after the values found in the range.

I.E.

Sheet2 range (F3:F100) has values ending with "*.csv", also has blank cells (actually they have formulas in them but the formula returns a "_"("_" is a space not an underscore)).

Sheet3 is a template formatted and containing some values that all new sheets will have.

If a sheet with the name of the value in the range already exists it should skip that cell and continue thru the range.

If the cell is blank it should skip that cell and continue thru the range.

If the cell value ends with ".csv" then it should create a new sheet (copied from sheet3) and name it the cell value.

Here is the code I have:
I am getting a run-time error and I think I might have something wrong since I am not that experienced with VBA.

Sub MakeNewSheets()

Dim LMainSheet As String
Dim LRow As Integer
Dim LContinue As Boolean
Dim Wsht As Worksheet

Dim LColMaster As String
Dim LColTest As String

'Retrieve name of sheet that contains the data
LMainSheet = Sheet2

'Initialize variables
LContinue = True
LRow = 3

'Start comparing with cell F3
LColMaster = "F3"

'Search column F values until a value of "*.csv" is found
While LContinue = True

LRow = LRow + 1
LColTest = "F" & CStr(LRow)

If LRow = 100 Then
LContinue = False
End If

'Skip cells that allready have sheets
If Wsht.Name = Sheets(LMainSheet).LColTest.Value Then
'Go back to Main sheet and continue where left off
Sheets(LMainSheet).Select
LColMaster = "F" & CStr(LRow)
End If

'Skip all cells that are blank or " ".
If Sheets(LMainSheet).LColTest.Value = "" Then
'Go back to Main sheet and continue where left off
Sheets(LMainSheet).Select
LColMaster = "F" & CStr(LRow)
End If
If Sheets(LMainSheet).LColTest.Value = " " Then
'Go back to Main sheet and continue where left off
Sheets(LMainSheet).Select
LColMaster = "F" & CStr(LRow)
End If

'when I find a cell that ends in .csv,
'Add New sheet named after that cell value.
If Sheets(LMainSheet).LColTest.Value = "*.csv" Then
'Copy sheet3 with sheetname equil to LColTest.Value
'remove the ".csv" from the end of the name
Sheet3.Select
Sheet.Copy.Name = Left(Sheets(LMainSheet).LColTest.Value, Len(Sheets(LMainSheet).LColTest.Value) - 4)
'Go back to Main sheet and continue where left off
Sheets(LMainSheet).Select
LColMaster = "F" & CStr(LRow)
End If

Wend

Range("A1").Select
Application.CutCopyMode = False

MsgBox "New Sheets Created!"

End Sub

any help would be great!

Thanks,

Hello world:

There is a program (I cannot access...) that creates a .CSV (Comma separated...) based on results acquired from a test. Separate file is crated for each day a test is performed. And each file will have only 1 sheet with data on it and that sheet will have Separate rows for each Test.

I am trying to create VBA codes that will:

1. Read each entry (each time a test is performed)
2. Create a new workbook or file (name it the same as the new entry)
3. Save that new file and display it for the user.

Note: There is a new file created for each day, but all of the entries have dates in them.There are more steps after step 3 but I pretty much got those. Please i would really appreaciate any ideas.

Here is what the entries look like, each row goes all the way to EK...

(8/2/2005 10:47) ( 19.6 ) (CS130 ) ( Fail ) ( Diode Pos 1)

Thanks,

David

Retrive information from one excel document and create a new document from form Hello,

This is my first time posting on this forum so I will try to explain as well as my english allows me to do

Ok, I'm sitting here with document "A", which is a excel document that is a Long, long list of companies forgotten payments.

And document "B" which is a form that I want to paste in for example Company name, adress in certain places....

Ok so far it should not be to difficult, but here comes the tricky part.

Document "A"s list is as follows.

Column A: Customer number (it says DL00 in front of every no which I do not want to copy)
Column B: Company name
Column C: invoice number
Column D: Penelty cost
Column E: Date when invoice should have been paid
Column F: Date when it was paid
Column G: C/O
Column H: Street adress
Column I: ZIP
Column J: City

What I would like to do is as follows;
1) I want to copy the custumernumber to Document "B" in A:8 with the DL00 cut out and Customer no: put in front of it

2) I want G,H,I,J and B put in to different spaces

3) I want everyone with the same customer number put in to the same document and put the cost of invoice 11111 to be put in A10 and the cost of invoice 22222 to be put in space A11...
So that I do not create 10 different documents for one company

4) I would like it to create a document for every company with the name of the company as the document name.

*Phew* ok this might be imposible I seriously have no Idea because programming in excel is not a strong side of mine :/

Thank you for your time.

Hi there,

I have a very large excel 2002 document with many worksheets. The worksheets are formatted identically but named randomly (like RMA3498 and IJ98M).

I am hoping that someone could give me some tips on how to create a formula or script.

I would like to create a new worksheet in the workbook that lists the data from each worksheet's F3 cell on a different cell in the new workbook. (I just want a list of what's in cell F3 for each sheet.)

I know how to do the =[Book1]Sheet1!$F$3, but since each page of the workbook is randomly named, I was hoping someone could explain how I could do a range of worksheets or some other workaround so I don't have to manually enter the formula for each cell.

Please let me know if you have a tip for this. I looked around extensively to find the answer before posting, but I couldn't find anything.

Thank you!

I'm in the process of creating a workbook for work. I need to have a
button on the spreadsheet that, when pressed, will create a new
workbook from the current one but will just display values and not
formulas.

These files will be sent to end-users, and it is very important that
they don't get their hands on the original worksheet WITH the formulas.

--
downer
------------------------------------------------------------------------
downer's Profile: http://www.excelforum.com/member.php...o&userid=31686
View this thread: http://www.excelforum.com/showthread...hreadid=513831


No luck finding an answer? You could always try Google.