Free Microsoft Excel 2013 Quick Reference

Publishing interactive excel (macro-enabled) file into a HTML webpage

Hello,

I was wondering if there is a way to publish a macro enabled interactive excel into a HTML webpage? I would really appreciate any kind of of suggestion/help!

Regards,
Abi


Post your answer or comment

comments powered by Disqus
Hi
I wish to save a macro enabled template file to a macro enabled file in D:Invoice2012 with a file name equivalent to the value of cell k3 in sheet 1.
I tried the following macro but ended up with a error

Sub saveFileMacro()
FullFileName = Application.GetSaveAsFilename("D:Invoice2012" & Format(Range("K3").Value) &
".xlsm", _
    "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm", 2, "Save File As")
If FullFileName <> vbNullString Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        FullFileName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Else
MsgBox "File not saved"
End If
End Sub
It works only till the "save as" screen and once I press save, it shows error "Runtime error '1004'. This extension cannot be used with selected file type and so on..
Originally I got this macro from http://www.excelforum.com/excel-prog...-template.html, customized it a little based on my requirement but still ended up with an error.

A little help would be much appreciated.

Thanks
Anand

Hi all, I keep getting a really annoying error message when working with macro-enabled files in excel 2007. If my files contain macros or custom functions, saving the file often fails and I get "your changes could not be saved to (filename) because of a sharing violation". Excel then offers to save to a temporary file, but when I try to open the temporary file, excel complains it is corrupt and cannot be opened. I would be SO glad for any fixes to this annoying problem. Thanks in advance!

Problem when I opening a Excel (Office XP) file into Excel (Office 2007) in relates to Chart

I have migrated the office XP into Office 2007. I have created a worksheet with lot of Simple charts in Excel XP. When I opening the file in excel 2007 the chart is disturbing. Eg: one 'X' Axis and Two 'Y' Axis data with simple line chart in Excel XP.  When I opening this file in excel 2007 the graph is not coming to the end of the line. Can I get solution for this.

Hi there!

So I have some VBA code that saves to network drive Excel files based on their store number, after it all finishes to process using the VBA script, it creates a log sheet which shows me all the Excel files that were saved and processed. The log file is just a plain sheet that records the locations of the save in the network drive and the excel file name.

I send a notification to employees, they use those files and fill in some data into the sheets and then at the end of the month, we need to grab those files back from the network drive for analysis. The files are in the same exact network path and the file names will not change, what would be the best way to grab those files back from the network drive and import them into a workbook using VBA ?

I was thinking of using something that would be able to work by selection, or I select the rows and then have Excel import the selected files into a single workbook, to give an example:

Let say that I have 5 rows in the Log sheet in column A and each row represents a successful save:

COLUMN A

Row1: networklocationlistsregion1Excel_doc_1.xls
Row2: networklocationlistsregion1Excel_doc_569.xls
Row3: networklocationlistsregion2Excel_doc_587.xls
Row4: networklocationlistsregion2Excel_doc_2.xls
Row5: networklocationlistsregion2Excel_doc_6.xls

I would like to be able to play a VBA script, it would allow me to highlight by selection the range that I want to process, so in this example I would highlight A1:A5 and then I would continue the VBA procedure and Excel would import those 5 Excel files into a single workbook, so 5 sheets in this case, each representing the document imported ?

I was looking into Application.GetOpenFilename, but I am not sure what the best way is yet, I figured I would start here and get every ones opinion.

Thanks a lot in advance for your help and assistance.

I Have changed from one computer to dell. I am trying to move my files from
one computer to the dell. I was using quattro pro 8 but have office pro 2007
on my dell. How do i convert quattro pro file into a office 2007 excel file?

I have created a spreadsheet that I want to send to various end users. The workbook contains 3 sheets. The first sheet is a welcome screen explaining the use of the spreadsheet. Additionally, I have written a macro assigned to a smiley that will take the user to the desired location on the second sheet. Certain cells are are also locked and the workbook is password protected to prevent altering. I have saved the workbook as a macro enabled file. However, when the file is opened, it says the macro is not contained in the workbook. I am sure it is something simple I have overlooked.

I would appreciate any help.

thx,
Stumped

I have a directory with many .docx files in it.

I am trying to build a macro to automatically copy and paste the contents of each .docx file into a new sheet in the .xls file.

So far I have pulled together the following snippet of code, which is suitable for pulling in a single, pre-named file into a specific sheet in the excel document:

Sub CopyFromWordDoc()

    Dim myWord
    Set myWord = CreateObject("Word.Application")
    myWord.documents.Open ActiveWorkbook.Path & myWord.PathSeparator & "Clients.doc"
    myWord.ActiveDocument.Select
    myWord.Selection.WholeStory
    myWord.Selection.Copy
    Worksheets("Input Data").Activate
    Range("A1").Select
    ActiveSheet.Paste
    myWord.Quit
    Set myWord = Nothing
   
End Sub
How I'm trying to change this, is to include a for loop so it does the copy&paste routine for every *.docx file in the same folder as the .xls file, and to increment the sheet it would be pasted to. The .xls file will start with only having one worksheet ("sheet1") in it.

Suggestions or recommendations?

Many thanks,

-Aaron

Hello everyone!

I tried many ways to convert a CSV file into a formatted Excel (.xls) file via VBA. I have a file with 5 lines (header included) and about 10 columns (delimited by commas). How can I format it via vba on button click action? Does anyone happen to know?

Thanks everyone!

I'm a bit of a novice with this, so bear with me...

I have lots of text files with data in them. I need to import each file into a different worksheet within the same workbook. After each set of data is imported I need to insert a chart on the same worksheet (all scales,axis,etc the same for each worksheet).

Now I've managed a code by using the record macro function that does this fine, but only for one set of data at a time. And it means I need to change the sheet number, chart title, text file name etc every time...

I'm sure there is an obvious way to sort this by looping, but I'm not sure how.

Here's my macro. The numbers in red are the numbers that I need to increase by 1 everytime it loops... and I need it to loop around 50 times.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 06/03/2008 by Me
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Experiment Results26_2_test1.txt" _
, Destination:=Range("A1"))
.Name = "26_2_test1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C1001"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(2).Delete
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Test 1"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (s)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Load (N)"
End With
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "Test 1"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 10
.MinorUnit = 0.5
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScale = 25
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Sheets.Add
End Sub

Any help would be greatly apprecited.

Hi,

I have a macro which creates a file and appends today's date onto the file name and then saves the file into a folder, the address is hard coded into the macro.

Is it possible to get the macro to save the file to a specific folder depending on the month and or Country name saved in the file name eg.

File name = Header_UK_13-Sept-07
macro saves file into folder called C:UKSept-07,

File name = Header_Ireland_13-Sept-07
macro saves file into folder called C:IrelandSept-07

Next month, UK file name may be 01-Oct-07 and the file should be saved into a folder called C:UKOct-07

There's numerous files created each month - not just one

So it's something like

If country = UK, month = Sept, Year = 2007 save file to c:UKSept-07,
If country = UK, month = Oct, Year = 2007 save file to c:UKOct-07

If month is a problem, would I just need a number of If statements in the save part of the macro ?

I could change the macro to save as month and not today's date if this makes things easier ?

Any ideas ?

thanks for your help
Steve

I'm a bit of a novice with this, so bear with me...

I have lots of text files with data in them. I need to import each file into a different worksheet within the same workbook. After each set of data is imported I need to insert a chart on the same worksheet (all scales,axis,etc the same for each worksheet).

Now I've managed a code by using the record macro function that does this fine, but only for one set of data at a time. And it means I need to change the sheet number, chart title, text file name etc every time...

I'm sure there is an obvious way to sort this by looping, but I'm not sure how.

Here's my macro. The numbers in red are the numbers that I need to increase by 1 everytime it loops... and I need it to loop around 50 times.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 06/03/2008 by Me
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:Experiment Results26_2_test1.txt" _
        , Destination:=Range("A1"))
        .Name = "26_2_test1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C1001"), PlotBy _
        :=xlColumns
    ActiveChart.SeriesCollection(2).Delete
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Test 1"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (s)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Load (N)"
    End With
    ActiveChart.ChartTitle.Select
    Selection.Characters.Text = "Test 1"
    Selection.AutoScaleFont = False
    With Selection.Characters(Start:=1, Length:=7).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 0
        .MaximumScale = 10
        .MinorUnit = 0.5
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScale = 25
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveWindow.Visible = False
    Sheets.Add
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 06/03/2008 by Me
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartTitle.Select
    Selection.Characters.Text = "Test 22"
    Selection.AutoScaleFont = False
    With Selection.Characters(Start:=1, Length:=7).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    ActiveChart.ChartArea.Select
End Sub


Any help would be hugely appreciated!

Hi,

What I want to do is to click a file in Windows Explorer and then drag
and drop it into a cell of an opened Excel file to create a hyperlink
to this file (i.e. later on if the linked file is a PDF file and if I
click the link, it will open the file in Adobe Reader. Of course, it
does not work in the way as I thought. Instead, Excel reads the file
into a worksheet. I wonder whether anyone has an idea how to achieve
this. It is just too tedious to use Insert -> Hyperlink to create the
link if there are many files to be linked. Thanks for any suggestion.

Paul.

Hi,

I have created one excel macro enabled sheet which has one ActiveX controls (command Button), upon clicking this, a form should appear.

This functionality is working on my system which is windows XP m/c.
But user m/c is windows Vista and there functionality is not working. The command Button itself became inactive.

What could be the possible reasons?
I checked the Tools->references tab and added the necessary dlls also.

Thanks in advance,
Venkat.

I am looking for a way to combine multiple XML files into a single spreadsheet. Each XML file when viewed in Excel will have the same header and a single row of information (mainly text based).

It is possible to import these XML files into a spreadsheet but it will only allow me to do this one file at a time. At this point we do not know whether we will be dealing with 10 files or 100.

I only have access to Excel 2003 and do not have access to the command prompt, a SQL server or the option to download an add on. I have the option of using VB but no real experience of how to fix it. I tried amending some code from here but I kept running into errors when running it.

I tried using the Data > Consolidate option but it didn't appear to work as I had hoped. The contents of the XML file are completed fields from a form and are mainly text strings.

Grateful for any help/suggestions on whether this is possible.

Hi All,

I would really appreciate if anyone can help me figure out this issue mentioned below:

I have created a few Macro enabled files and have saved them on my Mapped drive (WebDav connection). Now, whenever I open that file, it creates a temp file with ~$ sign in the beginning of the filename with its size showing as 0 KB(till here everything is normal and I understand that it will create temp file when the file is open).

But now the issue is, even when I close the macro enabled file, these temp files are not going away and if I delete them, it gets delete and as soon as I refresh the screen, it shows the files again.

I have ran all the available windows updates which includes updates for MS Office as well but with no resolution.

Once again, I am having this issue on Mapped drive(WebDav connection)

Please suggest on what could cause this issue and how to resolve this as it is really very annoying.

Thank you all for the help and I appreciate that people take initiatives to help others with the issues they are facing. Truly deserve a big applaud.

I have a spreadsheet with loads of postcodes on it.

What I want to be able to do is to somehow have all these postcodes formatted for web usage.

So on the spreadsheet it may have

BN11 3
BN11 5
BN17 8

And after what we want done is done it would read

BN113 <br>
BN11 5 <br>
BN17 8 </p>

Could it spit out the results direct into a .html file & then that can be opened & copied & pasted into the relevant space on the actual html file I wish to use?

I've attached a sample of my spreadsheet, thanks for any help.

Hi
I am trying to import a text file into a textbox on a form. It is to help the user to select the body of an email and the signiture.

thank you
Elliott skinner

I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be a merged
field. There will be approx 20 lines to be merged into various places in the
document.
How do you set up the word doc to receive these fields?

I am in need of some help I am trying to export some photos from my picture
file into a word document so I dont have to open up each individual picture
can someone help me with this

I would like to add my logo to the spreadsheets I circulate.

I can't find out how to paste a .jpg or other image file into a cell.

Hi,

Can an excel column be passed into a VB array in Excel and vice-versa? If so, I appreciate if some one could help me in this regard.

Thanks & Regards,
Krishna

Hi,
I have 5 csv data files called a.csv, b.csv, c.csv, d.csv, and e.csv. All of these files have the same structure. Is there a way to merge these files into a spreadsheet with a.csv, b.csv, c.csv, d.csv, and e.csv as 1st, 2nd, 3rd, 4th, and 4th sheets, respectively. I am using open office calc and don't want to merge the files manually anytime I create them. Any reply is appreciated.

Regards,
Reza

Hey there,

I am attempting to write code that does the following:

Within a excel macro enabled file, create a new worksheet for every day of the month and names the worksheet for each day in the format of something like (“Tues. 8-28-07 CA”). That is, I will create a file called “DailySpeningListAUG.xls” that contains this code. Run the code and have it create an individual sheet for the workdays (Mon thru Friday) within August. Then make a copy of this file, run an existing code DeleteAllWorksheets and then rename “DailySpeningListCurrent.xls”.

This I’ll run this code to create all the necessary tabs for the month of September.

Each sheet added will be a copy of an existing Sheet(“SpendListTemplate”) from a Template file located at a file_path.

Here is what I have so far but I really don’t know how to create the add sheet(S) dependant on workdays in month and rename then.

It opens the Tmeplate file and copies it to the Monthly’s CURRENT file.

Thanks for the help.
Terry

Code:
Dim lookupfilename As String
lookupfilename = "C:UsersTerryDocumentsCPS Automation ToolsOrderFormCreatorDailySpendTemplateFile.xls"        
Workbooks.Open lookupfilename
Workbooks("DailySpendTemplateFile.xls").Activate
Workbooks("DailySpendTemplateFile.xls").Sheets("SpendListTemplate").Copy _
            After:=Workbooks("DailySpendingListCurrent.xls"). _
                Sheets(Workbooks("DailySpendingListCurrent.xls").Sheets.Count)


Even though there are similar problems in a few threads, I could not find a solution to my problem, so I ask for help.

The problem is to sort of consolidate data inside several hundreds of Excel files within a directory tree, by copying values into a new Excel file, into a new row from each external workbook. The file name is exactly the same for each of the referenced Excel files, sheet name likewise, just as well the data structure of the sheets. We literally talk about hundreds of files containing more than a hundred data cells each.
When I started with this problem, I managed to write a master Macro-enabled Excel file, that has links to an unknown 'File1' and this solution was kinda OK with up to a 25-30 external files: after populating a single raw from a single external file I saved the new file and repeated the process with every external file, then copied the rows after each other into a new empty file. With more than 500 files I need a different solution, one that only needs to be started and a script would fetch the content of the referenced data cells for every existing files, then make a new row and move to the next file and fetch again, etc.

Since the number of referenced cells is also quite high, it may be a very frightening-looking script.
The first cell to be fetched is a string (a name), the second and third are dates, and all the rest is numeric.

I attach the master macro-enabled file to show a single row of the wished result (master.xlsm), and an example of the source file (example.xls).

Any type of solution would be appreciated, although VBScript is not my strength.

example.xlsmaster.xlsm


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