Free Microsoft Excel 2013 Quick Reference

VBA WinZip ZipX

Hi all,

I found a little code on Ron de Bruin site to zip files from Excel using VBA.
Works great!

Now I would like to see if I can get better compression as some of my .csv files are rather large.
Do I need to do anythingother than change the extension from .zip to .zipx?

Thx
w

Declare Function OpenProcess Lib "kernel32" _
                             (ByVal dwDesiredAccess As Long, _
                              ByVal bInheritHandle As Long, _
                              ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
                                    (ByVal hProcess As Long, _
                                     lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Sub ZipEm()
    '
    'Purpose:Zip specified files
    '
    '
    'Date       Developer       Action          Comments
    '---------------------------------------------------------------------------------
    '05/24/2009 Ron de Bruin    Last update     http://www.rondebruin.nl/zip.htm
    '02/16/2012 ws              Extracted
    '02/16/2012 ws              Modified
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim PathZipProgram As String
    Dim NameZipFile As String
    Dim FolderName As String
    Dim DimShellStr As String
    Dim strDate As String
    Dim DefPath As String
    Dim Password As String
    Dim strArchivePath As String
    Dim strDatabasePath As String
    Dim strDb1 As String
    Dim strDb As String
    Dim strDb2 As String
    Dim strDb3 As String
    Dim strDb4 As String
    Dim strDbName As String
    Dim intCount As Integer
    
    With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .DisplayAlerts = False
    End With
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("BackupDB")
    
    strArchivePath = ""
    strDatabasePath = ""
    strDb1 = ""
    strDb2 = ""
    strDb3 = ""
    strDb4 = ""
    strDbName = ""
    
    With ws
        strArchivePath = .Range("C4")
        strDatabasePath = .Range("C5")
        strDb1 = .Range("C6")
        strDb2 = .Range("C7")
        strDb3 = .Range("C8")
        strDb4 = .Range("C9")
    End With
        
    'Path of the Zip program
    PathZipProgram = "C:program fileswinzip"
    If Right(PathZipProgram, 1) <> "" Then
        PathZipProgram = PathZipProgram & ""
    End If

    'Check if this is the path where WinZip is installed.
    If Dir(PathZipProgram & "winzip32.exe") = "" Then
        MsgBox "Please find your copy of winzip32.exe and try again"
        Exit Sub
    End If

    'Create Path and name of the new zip file
    'The zip file will be saved in: DefPath = Application.DefaultFilePath
    'Normal if you have not change it this will be your Documents folder
    'You can change the folder if you want to another folder like this
    'DefPath = "C:UsersRonZipFolder"
    DefPath = strArchivePath
    If Right(DefPath, 1) <> "" Then
        DefPath = DefPath & ""
    End If

    'Create date/Time string, also the name of the Zip in this example
    strDate = Format(Now(), "yyyy.mm.dd hh.mm.ss")
    
'    Browse to the folder with the files that you want to Zip
'    Set Fld = CreateObject("Shell.Application").BrowseForFolder(0, "Select folder to Zip", 512)
'    If Not Fld Is Nothing Then
        FolderName = strDatabasePath
        If Right(FolderName, 1) <> "" Then
            FolderName = FolderName & ""
        End If

    For intCount = 4 To 1 Step -1
        'Set NameZipFile to the full path/name of the Zip file
        'If you want to add the word "MyZip" before the date/time use
        'NameZipFile = DefPath & "MyZip " & strDate & ".zip"
         strDb = "strDb" & CStr(intCount)
         Select Case strDb
            Case "strDb4"
                strDbName = strDb4
            Case "strDb3"
                strDbName = strDb3
            Case "strDb2"
                strDbName = strDb2
            Case "strDb1"
                strDbName = strDb1
         End Select
                
         NameZipFile = DefPath & strDbName & "_" & strDate & ".zip"
         On Error Resume Next

        'Zip all the files in the folder and subfolders, -r is Include subfolders
        'If you add -p, WinZip will store folder information for all files added,
        'not just for files from subfolders; the folder information will begin with
        'the folder specified on the command line.
        ShellStr = PathZipProgram & "Winzip32.exe -min -a" _
                 & " " & Chr(34) & NameZipFile & Chr(34) _
                 & " " & Chr(34) & FolderName & strDbName & Chr(34)
        
        'Use ShellAndWait to run the ShellStr
        ShellAndWait ShellStr, vbHide
    Next intCount
        
    'Tidy Up
     Set wb = Nothing
     Set ws = Nothing
        
     With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
     End With
End Sub


Post your answer or comment

comments powered by Disqus
Richie,

I just read your note posted a few months ago which included a routine for utilising WinZip. How could I modify the unzip section of your routine to simply open and view the zipped file. I don't need to save it, just need to view it, then manually close it.

Hello VBA guru's - -

I'm trying to have vba code that will zip a file, I've been trying this code below and from I can see winzip does not like the spaces in the folder name. My question is there a way around this? Besides removing the spaces? Note in the code below I did try this with a test folder name with no spaces and that seemed to work (the section commented out), just wondering if this code can be tweaked to allow the spaces? Thanks all for taking a look..

Sub ZipSafeDepositFile()
Dim ZipPath As String
Dim ZipIt As String
Dim Source As String
Dim Dest As String
Dim MonYear
    Dim mon
    mon = Format(Now() - 15, "mmm", vbLowerCase)
    MonYear = Format(Now() - 15, "mmmyy")    
ZipPath = "C:Program filesWinzip"

Source = "K:PROJ78007806 Safe Deposit Revenue EnahncementMonthly ListReports" & "" &
"7806_Monthly_" & MonYear & "_MailList" & ".txt.sda.exe"

Dest = "K:PROJ78007806 Safe Deposit Revenue EnahncementMonthly ListReports" & "" &
"7806_Monthly_TEST_MailList" & ".txt.sda" & ".zip"

'Source = "K:PROJ78007806test.txt"  TESTED this and it seemed to work ok.
'Dest = "K:PROJ78007806test.zip"   TESTED this and it seemed to work OK
'Note spaces important
ZipIt = Shell(ZipPath & "Winzip32 -a " & Dest & " " & Source, vbNormalFocus)

End Sub


Hi all,

I need to find code to allow me to unzip normal XP zip files (i.e. I do not have WinZip, or anything else installed, and neither will other users of the application).

I've managed to find the following code to allow me to zip files in XP without using anything other than normal VBA. The below snippet copies a single file (PathOfFile) into a newly-created zip file (PathOfZip), but it can obviously be modified to zip multiple files.


	VB:
	
 
Dim ShellApp As Object 
 
 'Create zip
Open PathOfZip For Output As #1 
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) 
Close #1 
 
 'Copy files into zip
Set ShellApp = CreateObject("Shell.Application") 
 
With ShellApp 
     
    .Namespace(CVar(PathOfZip)).CopyHere CVar(PathOfFile) 
    On Error Resume Next 'may hit error trying to read zip file whilst zipping is in progress
     
    Do Until .Namespace(CVar(PathOfZip)).Items.Count = 1 'keep script waiting until compressing is done
        Application.Wait (Now + TimeValue("0:00:01")) 
    Loop 
     
End With 
 
Set ShellApp = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need is similar code for unzipping files. Again, I'm not looking to have to install any third party code - I'm looking for something like the above. That said, I appreciate it may not exist, as I can't seem to find out how to do it anywhere...

I need to come up with a code that can run winzip application. Right now I am only able to open the winzip program but not able to extract the contents in it out. Please help.

This is the code that opens the winzip application:


	VB:
	
 SW_SHOW = 1 
Const SW_SHOWMAXIMIZED = 3 
 
Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _ 
(ByVal hwnd As Long, _ 
ByVal lpOperation As String, _ 
ByVal lpFile As String, _ 
ByVal lpParameters As String, _ 
ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) As Long 
 
Sub RunYourProgram() 
    Dim RetVal As Long 
    On Error Resume Next 
    RetVal = ShellExecute(0, "open", "C:Program FilesWinZipwinzip32.exe", "OC1ABC.zip", _ 
    "C:Documents and SettingsSGF065-FDesktop", SW_SHOWMAXIMIZED) 
    On Error Resume Next 
    RetVal = ShellExecute(0, "open", "C:Program FilesWinZipwinzip32.exe", "OC1DE.zip", _ 
    "C:Documents and SettingsSGF065-FDesktop", SW_SHOWMAXIMIZED) 
    On Error Resume Next 
    RetVal = ShellExecute(0, "open", "C:Program FilesWinZipwinzip32.exe", "OC1DE.zip", _ 
    "C:Documents and SettingsSGF065-FDesktop", SW_SHOWMAXIMIZED) 

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

Thank you

OzGrid / Et. Al.,

I've searched the internet and have found limited information on using VB to manipulate a WinZip file. Essentially, all that I've found is to "SHELL" some samples of this are:

WinZip Sample 1

This Sample is the code that I've adopted for my use.

My problem is this. My normal target .zip files are in the neighborhood of 75 - 80 Mb in size. Yes I know that they are huge..... (They are backup files for mainframe extracts ) Each zip file contains in the neighborhood of 2000 files. Currently, I have coded two ways of extracting data. Well really only 1.

1) I specify a location to extract all files. Which is time consuming ast it takes in the neighborhood of ( 5 min I thing for all files to get extracted ).

2) If I let the user specify a file filter parameter ( part of a file name ) while the WinZip shell is extracting files I'm searching all the file names in the dest directory and deleting the files that I don't want.

So what I want to know is it possible ( without buying software ) to be able to get the filenames of the files in a WinZip file.......

Does anyone have some VBA code that can zip files using the 'Compressed (zipped) Folders' program in XP.

I found some excellent code posted some time ago by Richie UK that can be used to zip and unzip files. But this uses Winzip.exe which XP doesn't appear to have.

Thanks in advance
Steve

Hi - I battle to write code to (i) compress files in a folder with winrar (not winzip) and (ii) to delete files in a folder.

Is there someone that can help me with this 2 types of code in excel VBA. Thanks in the mean time.

NS. The codes must be spreate

Hello all of you Experts,

I have been working (searching the web) on this in chunks for a couple of days and I am stumped. First off, I am a newbie to all this.

Here is my question?

I need to tweak an Macro (currently downloadsa CSV file type from the internet) in an excel workbook to trigger a WinZip Self-Extractor (.exe) located on a server that will run and copy a CSV data source file to a users hard drive.

Hope this makes sense.... here is a text staw dog of the VB code I require.

Summary:
Using Excel 2003
1. User opens excel (.XLS) workbook
2. Click on Macro button that runs
a. Data.ZIP exe file on ServerReportsPivot_Data_Source
b. Winzip runs and saves extracted file to C:ReportsPivot_Source_Data

Here is the VB code that does work beautifully for downloading files from an URL that was provided by Mav55th on this forum. I just need to figure out how to adjust it to address the ZIP executable (Data.EXE) question above:

Sub Copy_PSD_file_from_SP_Directory()
' PSD = Pivot Source Data
' Copy_PSD_file_from_SP_FC_Prf_Pltfrm
' Code below provided from Excel VBA User Forum (Mav55th)

' Status bar notification
Application.DisplayStatusBar = True
Application.StatusBar = "CSV data source file being copied from SharePoint, please be patient"
Dim i As Integer
Const strUrl As String = "http://enhanced1.sharepoint.com/teams/Pivot_Source_Data/Data.csv"
Dim strSavePath As String
Dim returnValue As Long

strSavePath = "C:ReportsPivot_Source_DataData.csv"
returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

' Status bar notification
Application.DisplayStatusBar = True
Application.StatusBar = "Refresh Process completed"

MsgBox "Pivot source data refresh process complete."

End Sub

------------------
Thanks!

Hey Guys:

I've been trying unsuccessfully to use Ron de Bruin's VBA routine for unzipping a zipped Excel file and depositing it in a designated directory. The code begins to run and opens the zip file, but no Excel file appears in the Winzip file contents window, and execution hangs. When I manually exit out of the Winzip window, the application completes execution and advises me to look in the designated directory for the unzipped file, but when I look, the file is not there.

I'm running Excel 2003 on Windows XP. Thanks for any help you can offer.

-Jonathan

(Subroutines)
Sub UnZip_ZipFile_1()
Dim PathWinZip As String, FileNameZip As String
Dim ShellStr As String, FolderName As String

PathWinZip = "C:program fileswinzip"
'This will check if this is the path where WinZip is installed.
If Dir(PathWinZip & "winzip32.exe") = "" Then
MsgBox "Please find your copy of winzip32.exe and try again"
Exit Sub
End If

FileNameZip = "O:REVENUE MANAGEMENTMIDT2007-072007-07-08 MIDT Daily Report.zip"
FolderName = "C:Documents and Settingssg0462391My Documents"

'Unzip the zip file in the folder FolderName
ShellStr = PathWinZip & "Winzip32 -min -e" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FolderName & Chr(34)
ShellAndWait ShellStr, vbHide

MsgBox "Look in " & FolderName & " for extracted files"
End Sub

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function Split97(sStr As Variant, sdelim As String) As Variant
'Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

(additional declarations at the top of the module)
Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

Hi all,

A query regarding the use of WinRar reminded me that I had a routine for utilising WinZip that others may find useful (thanks to Ivan for his posts on this topic that got me started in the right direction).

See the attached workbook for details (re-added 22/09/2004).

I have compiled a VBA code, borrowing some, adding some. It's intended purpose is, using an Excel Macro, to get information about all files in a directory/disk. Originally I used the the msoFileTypeAll, until I read that it did not always pick up .zip's as files vs folders.

I added a new section to pick up zip files in another fashion. On my machine it worked perfectly. Went through all the sub folders, picked up all files, looped, picked up all .zip files.

Moved it to another machine, suddenly, it picks up all files in main and subfolders of all types except .zip. It DOES pick up the first .zip file in the main folder, but no others, and ignores all .zips in sub-folders.

So it it definitely looping through the Subs to pick up all other file types (PPT, Txt, etc) and it is definitely seeing the first .zip file in the main folder. It is just not picking up the other .zip in the sub. So I am wondering if the other PCs are not recognizing the "Loop" function when it get to the GetFolder portion, altho it recognizes it on mine.

I compared the machines piece by piece, References, XP version, Office version, VB version, even the winzip version out of desperation. All the folder securities even. I am at a total loss. If anyone could please help, I would be eternally grateful. This is running on Office XO, thru an Excel Macro (Version 2003 SP2) with VB 6.3. Please excuse all the commenting out (Leftovers from trying to resolve the original problem)

========================
Sub PopulateDirectoryList()
'dimension variables
Dim objFSO As FileSystemObject, objFolder As Folder
Dim objFile As File, strSourceFolder As String, x As Long, i As Long
Dim wbNew As Workbook, wsNew As Worksheet
Dim InputDisc As String, InputMainFolder As String, InputSubFolder As String
Dim fso, fldr, f 'REMOVE

ToggleStuff False 'turn of screenupdating

Set objFSO = New FileSystemObject 'set a new object in memory
Set fso = CreateObject("Scripting.FileSystemObject") ' REMOVE
strSourceFolder = BrowseForFolder 'call up the browse for folder routine
If strSourceFolder = "" Then Exit Sub

'Workbooks.Add 'create a new workbook

Set wbNew = ActiveWorkbook
Set wsNew = wbNew.Sheets(1) 'set the worksheet
wsNew.Activate
'format a header
With wsNew.Range("A1:H1")
.Value = Array("File", "Size", "Modified Date", "Created Date", "Full Path", "Disc Name", _
"Main Folder", "Sub Folder")
.Interior.ColorIndex = 4
.Font.Bold = True
.Font.Size = 8
End With
With wsNew.Range("A2:H60000")
.Font.Size = 8
End With

With Application.FileSearch
.LookIn = strSourceFolder 'look in the folder browsed to
.Filename = "*.*" 'get all files
.SearchSubFolders = True 'search sub directories
.Execute 'run the search

'create InputBox
InputDisc = InputBox("Enter Disc Name: ", "Disc Name", "Disc ")
InputMainFolder = InputBox("Enter Main Folder: ", "Main Folder Name")
InputSubFolder = InputBox("Enter Sub Folder: ", "Sub Folder Name")

'Set objFSO = New FileSystemObject 'set a new object in memory
'strSourceFolder = BrowseForFolder

'Set objFolder = objFSO.GetFolder(strSourceFolder) 'get the folder
'x = 0
'For Each objFile In objFolder.Files
'rngDir.Offset(x, 0) = strSourceFolder
'rngDir.Offset(x, 1) = objFile.Name
'x = x + 1
'Next objFile

'Set objFolder = Nothing
'Set objFile = Nothing
'Set objFSO = Nothing

Set fldr = fso.GetFolder(strSourceFolder)
x = 0 'remove
For Each f In fldr.Files
If Right(f.Name, 4) = ".zip" Then
'MsgBox f.Name
With wsNew.Cells(2, 1) 'populate the next row with the variable data
.Offset(i, 0) = f.Name
.Offset(i, 1) = Format(f.Size, "0,000") & " KB"
.Offset(i, 2) = f.DateLastModified
.Offset(i, 3) = f.DateCreated
.Offset(i, 4) = f.Path
.Offset(i, 5) = InputDisc
.Offset(i, 6) = InputMainFolder
.Offset(i, 7) = InputSubFolder
End With
End If
x = x + 1
Next
Set f = Nothing

For x = 1 To .FoundFiles.Count 'for each file found, by the count (or index)
i = x 'make the variable i = x
If x > 60000 Then 'if there happens to be more than multipls of 60,000 files, then add a new sheet
i = x - 60000 'set i to the right number for row placement below
Set wsNew = wbNew.Sheets.Add(after:=Sheets(wsNew.Index))
With wsNew.Range("A1:H1")
.Value = Array("File", "Parent Folder", "Full Path", "Modified Date", _
"Size")
.Interior.ColorIndex = 4
.Font.Bold = True
.Font.Size = 8
End With
With wsNew.Range("A2:H7")
.Font.Size = 8
End With

End If
On Error GoTo Skip 'in the event of a permissions error

Set objFile = objFSO.GetFile(.FoundFiles(x)) 'set the object to get it's properties
With wsNew.Cells(2, 1) 'populate the next row with the variable data
.Offset(i, 0) = objFile.Name
.Offset(i, 1) = Format(objFile.Size, "0,000") & " KB"
.Offset(i, 2) = objFile.DateLastModified
.Offset(i, 3) = objFile.DateCreated
.Offset(i, 4) = objFile.Path
.Offset(i, 5) = InputDisc
.Offset(i, 6) = InputMainFolder
.Offset(i, 7) = InputSubFolder

End With
' Next objFile
Skip:
'this is in case a Permission denied error comes up or an unforeseen error
'Do nothing, just go to next file
Next x
wsNew.Columns("A:H").AutoFit

End With

'clear the variables
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set wsNew = Nothing
Set wbNew = Nothing

ToggleStuff True 'turn events back on
End Sub
Sub ToggleStuff(ByVal x As Boolean)
Application.ScreenUpdating = x
Application.EnableEvents = x
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant

Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

Set ShellApp = Nothing

Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = ""
If Not Left(BrowseForFolder, 1) = "" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function

Invalid:

ToggleStuff True
End Function

Hi there ,

I need to run winzip to unzip files in office xp macro .

My winzip command is
"c:program fileswinzipwinzip32.exe" -min -e "c:mydira1.zip"
"c:mydira1.zip.1"

I can run this command directly from command line,but when I run the macro,
using shell to run winzip ,vba keeps telling me "winzip parameter validation
error"

Can anybody help me ? What is the problem there ?

any messages from you will be appreciated.

thanks in advance .

I'm very new with VBA and I'm tryin to take the Max value of a range for a specific name.

I have this table

A B C Tom 8:00 AM 12:13 PM Tom 1:12 PM 6:05 PM John 10:39 AM 5:03 PM John 6:03 PM 10:30 PM Charles 11:00 AM 3:23 PM Charles 4:23 PM 8:00 PM

I need a vba code to put in column d the max value in column C for Tom, John and Charles (for each one of them)
I should be easy but I can't figure it out.
Thanks for the help.

I've created a workbook that links Excel to a SQL Server to retrieve data. I've had 3 other users testing it since Tuesday before I give it out to the hundred or so people that will ultimately be using it. It's been working fine until today when all four of us got error messages that stem from the same issue.

I have a user form with two text boxes, one for start date and one for end date. In the text box properties on the form itself I've linked each of these boxes to cells on one of the sheets, then set the format for these cells to date. I have the date stored in "mm/dd/yyyy" format to make it easier for the users but the SQL Server requires it to be in "yyyy-mm-dd" format so I've written VBA to create date objects, bring in these values, and reformat them. All was well until this morning when we all got an error from SQL about converting text into date/time. I troubleshot it down to my declarations:

If I Dim St as Date and Dim En as Date, neither value is formatted and both are sent to the server in the incorrect style.
If I Dim St, En As Date; St will format correctly but En will remain text.
I can only get it to work if I Dim St, En, DD As Date then not set DD to anything. I haven't tried it but I'm assuming DD will not work if I set anything to it.

Once I do this, the code works fine again. I'm worried because this happened "all of the sudden" (at least there's nothing any of us have changed settings wise) and I'm nervous that, once I distribute this to everyone, a new error will pop up in a week or month and version management will become next to impossible with 100 users. Has anyone seen something like this or know why it happens? I'd appreciate any insight.

I have got a table, with a range B1:CC200. I have been able to run a macro which pops up a form and shows the particulars of a selected month corresponding to those in the table. (e.g., say entries in column D pertaining to say, March 2012 are shown in the form). Problem is I want to be able to update the entries using the form, but no no success. Am quite new to vba but the code I tried to use on the command button after updating is shown below

Private Sub CommandButton1_Click()
TextBox1.Value = Application.Lookup(Range("A1"), Range("B1:CC200"), Range("B2:CC2"))
TextBox2.Value = Application.Lookup(Range("A1"), Range("B1:CC200"), Range("B3:CC3"))
.
.
.
TextBox20.Value = Application.Lookup(Range("A1"), Range("B1:CC200"), Range("B20:CC20"))
Unload Me
End Sub
Where A1 is the month in question, B1:CC200 the range of the database and row B2:CC2, and B3:CC3...., B20:CC20the data I need to update. .

I am using VBA to put a formula into a cell. The formula is


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But when I try to put this into a cell using the value option, I get a run time error 13: Type Mismatch. No problems while im compiling it but it comes up only during execution


	VB:
	
Sheets("Summation Table Template").range("B4").Value = "= IFERROR(VLOOKUP(LEFT(A5,11),'New Quarterly
Report'!A$11:E$37,3,FALSE), " - ")" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I try to put only the VLOOKUP, it works fine.


	VB:
	
Sheets("Summation Table Template").range("B4").Value = "= VLOOKUP(LEFT(A5,11),'New Quarterly
Report'!A$11:E$37,3,FALSE)" 

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

But I want the cell to not show #N/A when a value through vlookup is not found. Other formulas Iv tried are to use ISERROR with IF and also to store the formaula in a string and then put it into Range.Value but it still gives me a run time error 13. Why ?????

I have a named range $B$11:$M$14 of which I used the below reference to create. The cells in this range are randomly coloured and the user drags them out to re group in colour groups in another part of the worksheet.
I need this named range to remain un changed in its position but I need to allow the user to freely remove groups of cells (by dragging) from the range area without upsetting/changing the originally set range.
To achieve this I used INDEX to anchor the range in its relevant position to remain constant. (otherwise dragging large groups of cells out of the range causes the named range to adjust.)

My problem now is -
That I need to be able to add and remove columns within the named range using a VBA sub() in a different workbook (Workbook2) as necessary, and also adjust the named range accordingly as I add the columns.

The only constant is the top left cell of the range $B$11 if this helps? But the width of the range may vary depending on the Workbook being used by Workbook2 at the time.
How do I do this?

Starting named range is below.

	VB:
	
=INDEX(Sheet1!$A:$Z, 11,2):INDEX(Sheet1!$A:$Z, 14,13) 

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


I've decided to take up a VBA course and I'm deciding between learning Excel VBA on Excel 2003 platform as well as Excel VBA on Excel 2007 platform. The cost and duration for both courses are the same. Which course would you recommend I take?

-connor

I have been writing functions in vba for a template for designing heat exchangers. It returns #name errors when i try.
I have several functions I think i may have a problem with most of them. but they are written in such a way that
they depend on each other so that if the first returns an error the rest wont show anything.
The first is for calculating correction factor fc taken from Serth see code. I am stuck, see code.


	VB:
	
 
 'Correction Factor calculations for the LMTD
 'source Robert R Serth, heat Transfer principles and applications.
 'For any number of shell side passes and any even number of tube side passes as follows
 'LMTD is calculated as
 ' N is the nymber of shell side passes
 
 
Function Calcfc(Tis As Double, Tos As Double, Tit As Double, Tot As Double, N1 As Double, N2 As Double) As Double 
     
     'Correction Factor calculations for the LMTD
     'source Robert R Serth, heat Transfer principles and applications.
     'For any number of shell side passes and any even number of tube side passes as follows
     ' N is the nymber of shell side passes
     'R P and alpha are ratios used to calculate the correction factor
    Dim R As Double, P As Double, S As Double, alpha As Double, n As Double 
    Dim num As Double, den As Double, F As Double 'the numerators and denominators of the functions to ease writing the
functions
     
     
     
     'Where  Ta (Tis)= inlet temperature of the shell-side fluid
     'Tb (Tos) = outlet temperature of the shell-fluid
     'ta (Tit= inlet temperature of the tube side fluid
     'tb(Tot) = oulet temperature of the tube side fluid
     
     
    R = (Tis - Tos) / (Tot - Tit) 
     
     
    P = (Tot - Tit) / (Tis - Tit) 
     
     
    n = N1 * N2 
     
     
     
     
     
     
    alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) 
     
     
     
     
    If R = 1 Then 
        S = P / (n - (n - 1) * P) 
        num = (S * 2 ^ 0.5) 
        den = (1 - S) * WorksheetFunction.Ln((2 - S * (2 - 2 ^ 0.5)) / (2 - S * (2 + 2 ^ 0.5))) 
         
         
        F = num / den 
         
         
    Else 
        alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) 
        S = (alpha - 1) / (alpha - R) 
        num = ((R ^ 2 + 1) ^ 0.5) * WorksheetFunction.Ln((1 - S) / (1 - R * S)) 
        den = (R - 1) * WorksheetFunction.Ln((2 - S * (R + 1 - (R ^ 2 + 1) ^ (1 / 2))) / (2 - S * (R + 1 + (R ^ 2 + 1) ^ (1 /
2)))) 
        F = num / den 
    End If 
     
     
    Calcfc = F 
     
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any body with any idea.
Thanks

Hey all,
Given a data set such as the one I have attached, is there any way for me to list out the start stop time of each status without using VBA? I can code some VBA to do it but it will make the workbook much slower as it will require a loop to do it.

Thanks,
Ed

SampleData.xlsx

Hello Ozgrid Community,

I made a VBA script for Excel 2010 that essentially fetches data from a server then does it's magic. I am relatively new to VBA but managed to get the code to run in the background with no extra windows popping out. Except one. Every time it fetches the source file from the server a small "Download" window appears for a few seconds. Is there a way to hide this as well? I tried setting DisplayAlerts to False, which did not solve the problem. Ultimate goal is that the script does it's scheduled work with absolutely no interference/notifcation to the user.

Also, maybe this is a generic Windows 7 OS prompt as opposed to an Excel specific window. I am not sure. But if it isn't VBA I suppose I would need to modify the VBS that initiates the Excel process.

Any help would be appreciated. Thank you.

http://www.excelforum.com/excel-prog...data-sets.html

Hi All,

I have reported the above question in forum (excelforum.com) with the attachment.

Any help would be greatly appreciated.

Many Regards
Manav

Hi All,

Respected Members please accept my sincere thanks for all your esteemed help given through this channel.

I have attached a sample worksheet which I use in work. My aim is to find job nuumbers on sheet1 in the dataset on sheet 2(column A) and return the corresponding values iolumn B & C on sheet 2. Please note that one job number can be paid more than one time in the past months(eg. 3300023104), and therefore the requirement is to total the amount of money paid in all months, on a particular job reference. The output i require on sheet 1 is "month paid in" and "amount paid ". If a job is paid just once then simply get the month and amount on sheet 1. But i dont know if a paricular job like 3300023104 has been paid loads of times in several months, how can we list the months as well as the amount. If months is not possible then only amount can do...

My objective is to find out how much money has been paid before on the jobs mentioned on sheet 1 column A (my current month invoice). I already have a vba code to trace any duplicates within column A on sheet 1. For instance job ref 3300023104 has been repeated a few times on sheet 1.

Please advise at your convenience.

Thank you
Manav

I would like to know if there is a VBA code to help me sum up the values by date for the following fields (Begin, In, Out, Ending) based on the "Indicator" and "Type" criteria?

The details are in the attached spreadsheet. Thanks in advance for your help!

New to VBA:

I am trying to have my code read the a cell value in the active workbook then, based on that cell value, link to a specific page in another workbook.

For example, if the cell value for FrontPanel!E12 = 43532 I'd like to run a macro that will open [Patient ECGs(1.3).xls]43532!A2

Please advise? I'm wicked new to VBA, but the IF function is limited to only 6 levels and I have 6 different values for the cell that need to link to 6 different sheets, so the only way I figure I can make it work is through a VBA macro. Thank you in advance!


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