Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

macro date switch with vba

Hi,
I am trying to create a macro that changes date formats for 2 different columns. I can record a simple macro to change for specific cells but how do I change if for 2 specific columns. I want my cursor to stay where it starts out. So date 20060101 format switches to January 1, 2006, but keep the cursor on the specific line and be able to do it on any cell in column P. Also to change it back.

Maybe with VBA? I haven't a clue.
Here's the current:

	VB:
	
 dateeng() 
     '
     ' dateeng Macro
     ' Macro recorded 4/19/2006 by
     '
     ' Keyboard Shortcut: Ctrl+e
     '
    Range("P89:Q89").Select 
    Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy" 
    Range("P89").Select 
End Sub 
Sub dateback() 
     '
     ' dateback Macro
     ' Macro recorded 4/19/2006 by
     '
     ' Keyboard Shortcut: Ctrl+b
     '
    Range("P89:Q89").Select 
    Selection.NumberFormat = "yyyymmdd" 
    Range("P89").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks!


Post your answer or comment

comments powered by Disqus
Hi everybody,
I'm trying to find the command which introduces the date and time in an excell cell by using a macro with VBA.
Thank you if you read this and give me the answer.

I have written a small macro to format raw data from an accounting system to
a useable format in excel. The accounting system stores dates as dd.mm.yyyy.
As Excel wont recognise this as a date, the code I have written to convert
the date columns is simply:
Columns("E:E").Replace What:=".", Replacement:="/"
Columns("E:E").NumberFormat = "dd/mm/yyyy;@"
However, any date that wouldn't make sense in an american format
(mm/dd/yyyy) stays stored as text until you click in the actual cell. All my
settings are up as English UK, why is this happening? I have even tried
adding tricks into the VBA code that work to convert these date fields
manually, but don't in the VBA code! An example is typing "1" into an empty
cell, then copying, and paste special multiply across all the date range.
Works manually, but not in VBA!
The line where the error starts is the 'replacement' line. If I do this
manually excel immediately recognises the entire column as dates. If I do
this with VBA code it doesn't. Any ideas?
Thanks.
--
JM

Hi All,

I have a workbook with macros and vba code. Now is it possible to check and prompt the user to enable macros when they open this workbook if its not already enabled?

Maybe a prompt to click yes to enable both macros and trust all vba content???

I am using office 2010.

Thank you all.

Want to Copy the COntents of a cell and not the cell with VBA Macro

I am looking to keep the Contents of a cell ready to be pasted in an email textbox.
I have a bunch of emails appended in a cell with the help of a macro however going one step further I would like the contents of the cell and not the cell to be stored in the Copy ClipBoard and when I put my cursor in the To,CC,BCC text boxes then I can simply CONTRL+V or Right Click Paste

Warm Regards
e4excel

I have an excel “form” that people from different companies send me each month.
The fields are:

C2 = Company Name
C3 = Date of Inventory
C4 = Date of Receipt

After that, they give me a list of serial numbers, model names and colors:

A8 = 1234 B8 = Lemon C8 = Yellow
A9 = 2345 B9 = Apple C9 = Red
A10 = 345 B10=Grape C10=Purple
A11 = 678 B11=Apple C11=Red

This goes on down the “form”, then there is a second column with the same
data:

E8 = 1234 F8 = Lemon G8 = Yellow
E9 = 2345 F9 = Apple G9 = Red
E10 = 345 F10=Grape G10=Purple

E11 = 678 F11=Apple G11=Red

Sometimes people will “skip” a line for legibility.

I would like to read this data into a spreadsheet format like:

Serial Number Model Color Company Date1 Date2
1234 Lemon Yellow C2 C3 C4
2345 Apple Red C2 C3 C4

I’m not certain I explained this very well. I’m not familiar with VBA, but
it should work something like:
For I = 1 to TheEnd
If A8 <> NULL then
Row I+1 = A8, B8, C8, C2, C3, C4

I hope you can figure this out and help me.
Thanks a bunch!

Before I explain what I am trying to do, let me describe the data layout that I want the macro to play with.

I have a table that is fixed at 13 columns by "N" rows (anywhere from 20-30 rows). This table is pulled from a SQL report I created each day, and I have to perform the same routine to massage the data each day. (I have been exploring ways to do this through SQL but have been unsuccessful) The columns:

A - Name (primary key, only one entry per employee)
B - Hours worked
C - Deptartment (retail, international, or domestic)
D - # of Sales
E - # of returns
F - # of receipts
G - # of Retail returns
H - # of Retail receipts
I - # of int returns
J - # of int receipts
K - # of dom returns
L - # of dom receipts
M - # errors

Employees will work in up to three departments per day (retail, international, or domestic). If an employee only works in “retail” than he will have integers greater than zero in columns G and H. I, J, K and L would all have 0 in them. If an employee works in both “international” and “domestic” than he would have integer values in columns I, J, K, and L but 0 in G and H, and so on.

What I do is find employees who worked in multiple departments, and then create an additional row for each dept they worked in, and move the data from that dept into a single column (E and F). Goal is to have the department breaks in row form and not column. By default E and F are empty are simply used to eventually hold the values from G through L. Consider this example:

"Bob" is in row 2. He worked in Retail and Domestic, therefore there is data in G,H,K,L but I and J are 0.

1. I insert one row below Bob (new row 3)
2. I copy the data from G & H and paste it to E & F of row 2
3. I change bob’s dept code in column C to “RET” of row 2 because this row represents his work in the retail department.
4. I move to row 3 and enter the name “Bob” in A3 (same value as A2)
5. I copy the data from K and L and paste to E & F of row 3
6. I change bob’s dept code in column C to “DOM” because this row represents his work in the domestic department
7. I am done with Bob, now I move on to the next row
8. Row 4 has Alice. Alice only worked in one department, international
9. I copy the values from I & J and paste to E & F of row 4
10. I change her dept code in column C to “INT”
11. I am done with Alice since she only worked in one dept she only gets one row
12. Next I move to row 5. Steve worked in all three departments, therefore he needs 3 separate row.
13. I insert two rows under Steve creating a new row 6 and row 7
14. In row 5 I copy the data in columns in G&H to E&F, then set his dept code in column C to “RET” because this row now represents the retail work he did.
15. I move to row 6 and set cell A6 to “Steve” (same value in A5)
16. I then copy the data from row from I&J and paste to E&F. Next I change the dept code in column C to “INT” because row 6 will now represent his international work
17. I move to row 7 and set A7 to “Steve”, same value as A6
18. I copy the data from K&L and paste to E&F. Next I change the dept code in column C to “DOM” because this row represents the work he did in domestic
19. I repeat this logic for each row (This routine will always begin at row 2 since row is a fixed header row, the number of rows I start with ranges anywhere 20-30.
20. Once I complete the last row I delete columns G through L since the data is now stored in columns E & F and broken by row.

The End.

I have not been able to script this myself, I am good with recording macros but have limited VBA experience. Any help is greatly appreciated. I realize this is a lot to ask, but this board is always helpful so I am giving it a shot.

I have a macro that works on "ctrl d" assigned to a button.

i decided to have that macro run auto with vba worksheet_change on a single cell that changes dates.

When it run auto, some of the sort functions fail. If i then hit the button it works fine.

From the keyboard or button alls well, from the vba code it doesn't sort? no errors just doesn't sort

any ideas?

I am new to the forum, but would like to express appreciation for any help in advance. I came across the following post as I tried identifying a solution to my problem:

http://www.excelforum.com/excel-prog...ther-file.html

I have sheets that are being created daily as well, and I want to copy the data over to a master sheet to keep a daily record of the totals generated from the query that is generating the daily Excel files.

I copied the code from the above mentioned post and tried modifying it for my specific use. The macro runs, but returns an error saying that the file was not processed. I am pretty novice with VBA and am trying to troubleshoot the code to see what additional changes I would need to make for it to function properly. I am not sure, but I think it may be something involved with either the date format or the name of the file being processed. Nonetheless, I 'd rather ask for help than scratch my head forever.

I have attached two files to help show what is taking place.

Also, here is a copy of the code with the changes that I made:

Sub CollectData()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      11/23/2010
'Summary:   Open all the files in a specific folder and add key data to database
'           moves imported files to "imported" folder to preclude repeats

Dim fPath As String, fDone As String
Dim fName As String, fDate As String
Dim wsData As Worksheet, wbImp As Workbook
Dim dRow As Long, ErrMsg As String

'Setup
    Application.ScreenUpdating = False
    Set wsData = ThisWorkbook.Sheets("Data")
    fPath = "S:Customer Relations-DNRProcess ImprovementQGC Customer Service Dept. ProjectsMisc. Projectse-Bill
ProjectQueriesGrogNew Data"
    fDone = "S:Customer Relations-DNRProcess ImprovementQGC Customer Service Dept. ProjectsMisc. Projectse-Bill
ProjectQueriesGrogProcessed/"
    
    fName = Dir(fPath & "*.xlsx")
    On Error Resume Next

'Collect data
    Do While Len(fName) <> 0
        fDate = Format(Left(fName, InStrRev(fName, ".") - 1), "DD-MM-YY")
        If IsDate(fDate) Then
            dRow = wsData.Range("A:A").Find(fDate, LookIn:=xlValues, LookAt:=xlWhole).Row
            If dRow <> 0 Then
                Set wbImp = Workbooks.Open(fPath & fName)
                With Sheets("Sheet1")
                    .Range("C2").Copy wsData.Range("B" & dRow)
                    .Range("C3").Copy wsData.Range("C" & dRow)
                    .Range("C4").Copy wsData.Range("D" & dRow)
                    .Range("C5").Copy wsData.Range("E" & dRow)
                    .Range("C6").Copy wsData.Range("F" & dRow)
                    .Range("C7").Copy wsData.Range("G" & dRow)
                    .Range("C8").Copy wsData.Range("H" & dRow)
                    .Range("C9").Copy wsData.Range("I" & dRow)
                End With
                wbImp.Close False
                Name (fPath & fName) As (fDone & fName)
            Else
                ErrMsg = ErrMsg & vbLf & "    " & fName
            End If
        Else
            ErrMsg = ErrMsg & vbLf & "    " & fName
        End If
        
        fName = Dir
        dRow = 0
    Loop
                    
If ErrMsg <> "" Then MsgBox "The following files were not processed:" & vbLf & ErrMsg
Application.ScreenUpdating = True
End Sub

'Note the fPath and the fDone strings...

    'fPath is the directory where the files are found, remember the final  in that string

    'fDone is where the files are moved to after they are imported so you know they are done. Create that directory if
needed.

    'Errors are buffered and presented as a list of files that were not processed at the end. Try putting a garbage file in
that directory that isn't named for a date and you'll see.
Thanks again in advance

I have a macro that checks a barcode to see if it a valid barcode. if it isn't it makes the cell red to show there is an error. this all works great except that I use conditional formatting to color band every second row to make the data stand out because there is over 3000 lines in the spreadsheet. my problem is that while the if an error occurs where there is no color it is fine but if there is color it hides the error color so the error doesn't show. is there a way around the conditional formatting either by doing the color banding with VBA or to makeconditional formatting's priority lower. any help would be much appreciated. i've included the code i use below.

Thanks in advance,

Chris


	

	
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

LastRow = ActiveSheet.UsedRange.Rows.Count

        With Target
          
          If .Count > 1 Then Exit Sub
            If Not Intersect(Range("L2:L4000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                   .Offset(0, 5).ClearContents
                Else
                    With .Offset(0, 5)
                        .NumberFormat = "mm/dd/yyyy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
          
          If .Count > 1 Then Exit Sub
            If Not Intersect(Range("M2:M4000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                   .Offset(0, 5).ClearContents
                Else
                    With .Offset(0, 4)
                        .NumberFormat = "mm/dd/yyyy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If

          If .Count > 1 Then Exit Sub
            If Not Intersect(Range("N2:N4000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                   .Offset(0, 4).ClearContents
                Else
                    With .Offset(0, 3)
                        .NumberFormat = "mm/dd/yyyy"
                        .Value = Date
                    End With
                End If
                Application.EnableEvents = True
            End If
            
        End With
'    End Sub
    
'Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r           As Range
    Dim cell        As Range
    Dim s           As String
    Dim i           As Long
    Dim iSum        As Long

    Set r = Intersect(Target, Columns("L:N"))
    If r Is Nothing Then Exit Sub

    On Error GoTo Oops
    Application.EnableEvents = False

    For Each cell In r
        With cell
            s = Replace(.Text, " ", "")

            If Not IsNumeric(s) Then
                .Interior.ColorIndex = xlColorIndexNone
            
            Else
                Select Case Len(s)
                    Case 8
                        .Value = Format(Val(s), "0000 0000")
                        .Interior.ColorIndex = xlColorIndexNone

                    Case 12
                        .Value = Format(Val(s), "000000 000000")
                        .Interior.ColorIndex = xlColorIndexNone

                    Case 13
                        .Value = Format(Val(s), "0 000000 000000")
                        .Interior.ColorIndex = xlColorIndexNone

                    Case 14
                        .Value = Format(Val(s), "0 00 00000 000000")
                        .Interior.ColorIndex = xlColorIndexNone

                    Case Else
                        .Interior.ColorIndex = 3
                         MsgBox "Not a valid UPC Format"
                End Select

                If .Interior.ColorIndex = xlColorIndexNone Then
                    iSum = 0
                    For i = 1 To Len(s) - 1
                        iSum = iSum + Val(Mid(s, i, 1)) * IIf(i And 1, 3, 1)
                    Next i 'formatting in the code.
                    iSum = WorksheetFunction.Ceiling(iSum, 10) - iSum
                    If Val(Right(s, 1)) <> iSum Then .Interior.ColorIndex = 3
                End If
            End If
        End With
    Next cell

Oops:
    Application.EnableEvents = True
End Sub


Hello to all,

I am a complete Noob with vba. (i know excel though). I need to sort a database in excel 2010 using a vba macro. I have got this so far..

**********************************
Sub Macro12()
'
' Macro12 Macro
'

'
Range("A1:F1").Select
Range(Selection, Selection.End(xlDown)).Select

' Here should come a string which sorts the ever changing above selection on header A1 and E1

Selection.End(xlDown).Select

End Sub
***********************************

Now all this does is select the (ever growing) database-cells (filled cells). Next is: sorting the thing (first on header A1 and then on header E1). After that i need to jump to the last filled cell in column A. Then the macro should end.

Can someone please help or point me in the right direction??

ps: just to be clear about it. In the first row are headers ('fieldnames') and there is a 'autofilter' on that row.

Substituting Conditional Formatting Code with VBA Macro

Is it possible to Susbtitute Conditional Formatting with VBA Macro?
I need to desperately write a code in VBA but do not know how to implement the same..

AS in CF we write the codes for particular cells however they apply to a Range, so how to do the same in VBA ?

Warm Regards
e4excel

I have written a small macro to format raw data from an accounting system to
a useable format in excel. The accounting system stores dates as dd.mm.yyyy.
As Excel wont recognise this as a date, the code I have written to convert
the date columns is simply:
Columns("E:E").Replace What:=".", Replacement:="/"
Columns("E:E").NumberFormat = "dd/mm/yyyy;@"
However, any date that wouldn't make sense in an american format
(mm/dd/yyyy) stays stored as text until you click in the actual cell. All my
settings are up as English UK, why is this happening? I have even tried
adding tricks into the VBA code that work to convert these date fields
manually, but don't in the VBA code! An example is typing "1" into an empty
cell, then copying, and paste special multiply across all the date range.
Works manually, but not in VBA!
The line where the error starts is the 'replacement' line. If I do this
manually excel immediately recognises the entire column as dates. If I do
this with VBA code it doesn't. Any ideas?
Thanks.
--
JM

Hi Had a quick search around here and on Google and can't find the answer I'm looking for. I've created some vba code that populates a java based console window from excel as we have users complete forms in Excel which are then populated in a Java based program window. We're using Excel 2003 (because the company is likes it old school) and sendkeys (yes I know it can be unreliable but there's nothing else I believe I can use) and I know I can use sendkeys to Alt + Tab between windows but I'd ideally like something that eliminates this I've had success before with internet explorer windows using the code below (there was more to it than that but it seemed extraneous to post the whole thing) and was wondering if anyone knew anything similar or at least workable for a Java window that starts "Service Name" I'm decent enough with VBA but might get easily confused if any replies get very technical. In summary - populating the console - sorted Switching screen from Excel to Java - problem Appreciate any help!

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've recorded a macro and need some assistance with VBA.


	VB:
	
 Import_Data () 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;D:ZYZ_Sample", Destination:=Range("$A$1")) 
        .Name = "ZYZ_Sample" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 437 
        .TextFileStartRow = 1 
        .TextFileParseType = xlFixedWidth 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = False 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) 
        .TextFileFixedColumnWidths = Array(10, 11, 7, 27, 9, 7, 4, 12) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
    Range("E:E,H:H").Select 
    Range("H1").Activate 
    Selection.NumberFormat = "$#,##0.00" 
    Columns("I:I").Select 
    Selection.NumberFormat = "000#" 
    Range("A1:I1").Select 
    Selection.Font.Bold = True 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Range("A2").Select 
    With ActiveWindow 
        .SplitColumn = 0 
        .SplitRow = 1 
    End With 
    ActiveWindow.FreezePanes = True 
    ChDir "C:UsersDesktopReport" 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:UsersSmithDesktopReportGet My Report.xlsx", FileFormat:= _ 
    xlOpenXMLWorkbook, CreateBackup:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So basically it looks for a file on the D: drive. In the sample above, I manually chose the file name, but I was wondering if it's possible to have the VBA code generate a file open dialogue box so that the user can double click on the file to import.

Either that, or have a message pop up:

"Please type the name of the file you wish to import"

The data file to be imported will always be text and there's no file extension set for it. In another words, the file extension is blank.
Excel will import it just fine whether I add the *.txt file extension or just keep the filename as is. In the macro I recorded it's called "ZYZ_Sample" but it could be anything really; e.g. excel export, whatever... It always changes.

After it's imported, it goes through the steps of formatting the columns as necessary (see macro) and then it changes the directory for the File Save location. I would like the VBA to give it the name of the original data source, "ZYZ_Sample" (whatever it might be for a given month).

"C:UsersSmithDesktopReportZYZ_Sample.xlsx"

If "ZYZ_Sample.xlsx" exists; then:
Save As: Get My Report - MODIFIED.xlsx"

Then generate a pop up box for the user:

VbMessageBox
"Your Report has been saved as:
xxxxxx located in your report folder."

where xxxxxx = the name of the file.

I know the obvious question is how do you determine the file name? The file name is always going to equal the original data source on the D: drive.

Cross posted here.

Hey everyone,

We have a software that we use for budgeting, and every now and then, we have to export and validate the data in Excel to be in sync with the system. What I would like to do is after extracting the data into a spreadsheet, compare it with VBA and create a report in a seperate sheet, that shows the differences between sheet1 and sheet2. The data has 11 different dimensions from column A to K and is in the same order both in the master and recon files.
AU ID
Accounting Unit
Description
Lawson Division
Division
Product Line
Mid/View Consolidator
Mgr Responsible
Cost Center
Opex Rpt
Allocation Pool

I have found a compare macro which only compares and finds the exact match of the sheets in exact cells
Sheet1(A1) 1 Sheet2(A1) 1
Sheet1(A2) 2 Sheet2(A2) 2
Sheet1(A3) 3 Sheet2(A3) 3
Sheet1(A4) 4 Sheet2(A4) 5
Sheet1(A5) 5 Sheet2(A5) 6
If this was the case then everything as of cell A4 would be marked as an error, even though I have the value in a different cell in the next sheet.

I hope I could explain my problem clearly...

Thanks for any help

Ali Murat

I copy a module to a new workbook created with vba which also has a picture copied using vba, I want to assign this macro to the picture using vba from the original workbook.

Hi
I want some help to do the following

Save the workbook in c:my documentsAnt
with name "trading" and date

so if today is 3rd then it will save the file as "trading 03/08/2006".xls
in c:mydocumentsAnt

if it's 4th then "trading 04/08/2006".xls

thx for the help

A macro I recorded to paste dates from a text file gives incorrect results.
I have recorded the macro using macro record. While I record the result is correct. However when I replay the macro the date returned is incorrect. Note my date format is d/mm/yy. The error only occurs for dates with the day less than 13! (Something to do with the month range 1 to 12???)
eg: date copied is 1/11/05 the result is 11/1/2005, however the date 28/10/05 copies correctly. BUT I repeat the error does not occur during live recording - it only occurs when playing the macro.
I have tested this on a second PC and it still occurs. I am using English (Australia) for region. I tested English (US) and the error still occurred.
I attach 2 files dates.txt and dates.xls Simply copy the dates in dates.txt, then in an excel file record a macro and paste the dates. Stop macro recording. Now select another column and replay the macro. dates.xls has a macro (macro1) already. Cheers!

Hi All,

I am working on a chart that I generate with vba. Essentially, I extract a bunch of info from a workbook, make a new workbook, insert info in table format and make bar graph from this table. The chart is graphed as "percent done" vs project name because project sizes vary greatly, but I would like to be able to put the "Requested" number (ie project size) at the top of each category.

The table looks as follows:

Library %Finished Requested Due Date #Finished
cn_kol6f1b3 33.07% 1152 production 381
cn_la10f1f8 78.23% 960 production 751
cn_rq7f11h3 59.06% 960 production 567
dw_ssi16s06 72.14% 384 production 277
dw_ssi16s07 72.66% 384 production 279
rw_a5mgacdm 77.66% 5896 22-12-03 4579
bf_cschxxxx 11.00% 3456 22-12-03 380

The 1st column is the x-axis. The 2nd col is the y values. Cols 4 and 5 are concatenated and used as labels. I would like to put the values in column 3 at the top of each category on the chart.

So, my chart will have a category called cn_kol6f1b3 which has a value of 33.07% and a label that reads "production (381)". At the 100% line, above this category will be the value 1152 (which is the number of samples that = 100% for this category).

The chart works except for being able to put the "Requested" value for a category on it.

Hope you can help.

Cheers,

Jen

ps I can't seem to find where to upload files anymore. Are we getting this functionality back or am I just looking in the wrong place?

Hi

I am estimating to Extend a business system report engine in Excel 2007 with VBA and storeprocedures. My thought is that my Business system (ERP) has a key fields that isexported out into the Excel sheet but I am missing 5 fields in this particular report. This fields cannot be inserted by the standard report engine so I thought a buttonin Excel 2007 wil do the trick. So my question is wich oneof folloowing suggested approacheswill be the best out of a performance view:

1) A macro that iterates through the existing data in the Excel worksheet to get the key Identities so that a stored procedure can be run for every one af the rows in the excel sheet and add the 5 missing fields

2) Extract a big dataset from db instead of having a store procedure being executed for each and every row in the excel sheet. This dataset will then in turn be iterated by the macro so the missing 5 fields can be put ineach correct row by the macro

3) any other suggestions?

I am automating addition of conditional formatting with vba. When I use the code generated by the macro recorder to add data bars(pasted below) and save the file, I get an 'Excel found unreadable content in workbook' error when I reopen. If I chose to recover, all conditional formatting is gone. This only occurs if I use the vba macro to add data bars, if I do it manually the error does not occur. Thank you for your help.


	VB:
	
Selection.FormatConditions.AddDatabar 
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True 
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
With Selection.FormatConditions(1) 
    .MinPoint.Modify newtype:=xlConditionValueLowestValue 
    .MaxPoint.Modify newtype:=xlConditionValueHighestValue 
End With 
With Selection.FormatConditions(1).BarColor 
    .Color = 13012579 
    .TintAndShade = 0 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all

I'm wondering if it is a all possible to have a VBA macro call another program (like a Perl scripts, or a Python script) from within VBA?

I have no idea if this is possible. I know that people often write programs which call other programs, but would have no idea where to get started on this.

If it IS possible, can you tell me if it is is extremely complicated?

I have to have a VBA script read some complicated text files, and parse and extract information from them. Perl is so great at this, and I seem to get a headache trying to do any pattern searching and matching with VBA. If it was instead possible to write a Perl script, which is then run by VBA, that would make my life so much easier... :-)

Thank you all in advance.
Emma

Hi,
i want to have a VBA sub that can automatically assign 7 day to a specific week. for example 9-Jan-06 to 15-Jan-06 is week 2. And i have this code below:


	VB:
	
 OKButton_Click() 
     
    If ListBox1.Text = "Week 1" Then Call Filter 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the Filter is a sub as below:


	VB:
	
 Filter 
     
    With Sheet1 
        .AutoFilterMode = False 
        .Range("A1:I1").AutoFilter 
        .Range("A1:I1").AutoFilter Field:=3, Criteria1:=">=9-Jan-06", _ 
        Operator:=xlAnd, Criteria2:="

Hi i having a problem with VBA Forms. I have a macro that run correctly when a value is entered and i click the OK button. However when i click the x on the top right hand corner, the macro still runs will a 0 value. Is there a way that i could unload the form if x is pressed?


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