Free Microsoft Excel 2013 Quick Reference

Vba to open workbook Results

Hi All

I keep on getting the "Not enough system rosources to display completely" error message in my workbook. I've gotten this error before in VBA (with other workbooks) but now also in Excel.

Every time I enter anything I get the error, reopening the workbook fixes it but only until I open VBA, then it starts again.

I've done a couple of searches but its as if no one ells has come across this before? (Can that be?)

Any help (or sympathy) will be appreciated.

When processing workbooks via VBA, on closing the workbook I sometimes get a request to respond to whether I want to save the clipboard.

In the same way that there is the option to open a workbook without refreshing links


	VB:
	
 
workbooks.open updatelinks:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I assume there is something on the order of:


	VB:
	
 
workbook.close   DontSaveClipboard:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Question #1 is: What is the parameter syntax for saving or not saving the clipboard?

Question #2 is: Where would I go to look up these parameter settings instead of having to come back to the group every time I find the next one I need?

TIA,

Far Farley

Hello,

I want to return the names of all workbooks in a folder that satisfy the condition that a given column is not empty between rows 4 and 110. I would like those names as a list in a new workbook called Names.xls. I know a little about VBA code but this is more than I can write, unfortunately... thanks. I actually have code that will search all workbooks in my folder, so I could use a sub that check a given workbook for the condition and returns its name to the next available cell in column A.
Thanks

My question is the following:

- I use VBA in Excel, and would like to use Excel itself as a data source
(Using ADO). I'd like to keep a table in excel and use SQL expression
power to manipulate the data in the table (selection, grouping, filtering.),
rather than using the usual Excel lookups.

- This does work, but I have an annoying secondary effect with the following
environment:

1) I have an (unrelated) excel session already open (Session 1) on my
Windows desktop.

2) I open a new, fresh, Excel session (Session2) and open my file, with the
table, and the VBA code.

3) I run the code in my file, Session2: it does work, but the unwanted
effect is that my file opens itself again, as Read-Only, in the other
Session1!

I do not know what went wrong. It could be great if you could put me on the
right tracks.

Thanks in advance for your help.

Regards


	VB:
	
 Simulation3() 
     
    strPathExcelFile_FILTER = ThisWorkbook.FullName 
     
    Set objConnection = CreateObject("ADODB.Connection") 
    Set objRecordSet = CreateObject("ADODB.Recordset") 
     
    objConnection.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strPathExcelFile_FILTER & ";" & _ 
    "Extended Properties=Excel 8.0;" 
     
     
    objRecordSet.Open "SELECT COUNT(*) AS resultat FROM [SHEET1$A1:IV20] 
    WHERE [PX_LAST] > 20", objConnection, adOpenForwardOnly, adLockReadOnly 
     
    Simulation.Label2.Caption = objRecordSet.fields("resultat") 
     
    objRecordSet.Close 
    objConnection.Close 
    Set objConnection = Nothing 
    Set objRecordSet = Nothing 
     
End Sub 

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


I recently deployed a single-page spreadsheet intended to be added to existing workbooks (it generates a hiearchical view of pricing in a WBS structure), and it includes some UDFs and subs.

I was hoping I could save it as a tempate, but when inserted as a template, it didn't bring along its code module. I ended up instructing someone to copy the sheet to the intended workbook, and had them open the VBE to drag the module over. There is surely a better way!

I would prefer not to train people to use add-ins or mess with Personal.xls.

Thanks in advance for your suggestions.

Hi everybody!

Is it possible to check, with VBA code running some sort of "If" function in the background in an open workbook within which the VBA code is placed, whether a user is opening (or attempting to open) the VBA Editor?

This should see any attempt being made, whether the user uses Tools>Macro>Visual Basic Editor, Alt + F11 or right clicking on the Excel icon top left to "View Code".

If it is possible, could anyone please help me with VBA code that would do the checking? As you probably know by now - I am pretty useless beyond recording macros and plagiarising (I don't think messrs Funk or Wagnell will recognise that concoction!) code from you guys!

Thanks in advance

Harry

Dear experts,

I would need your help. I am complete beginner with VBA (started to read this forum yesterday). I am facing a problem applying VBA. I am working with excel files having some hundreds.. I would like to copy certain rows from excel books and paste them to a sheet of a new book. If possible having the link at the end of the row where the rows were copied from. This is the basic concept.

I have found some parts that could be useful to solve the problem but I would need your help to put the parts together with the necessary changes.

Details:
I have a folder “2006” having 12 sub-folders for the months and these also having sub-folders for the days (for workdays). All contains excel files (some hundreds all together) of same type that could be identified by “FB” and “quet” characters in their titles (the rest of the titles different of course).

I would like to use these files (all sheets of each file) as source (find them somehow) even if they are closed (no need to open them), select cell content of column range A to AD, where A column contains numbers of 1 to 6 (row number is not fixed) and actually copy data from B to AD range if B cell is not empty (there are cells merged vertically and horizontally within this range if it matters).

I would like to paste all these selected rows to a new sheet of an open book (if it needs to be saved, it can be any). And if it is possible I would like to have a link to the file where the row was copied from in the last cell of the row.

I hope could explain it but if there is something wrong please tell me. Any help is appreciated. Please talk to me “low level” because this is my second day with VBA.

attis


	VB:
	
 DoBox() 
     
    ActiveSheet.Cells.Find What:="", LookAt:=xlWhole 
    Application.CommandBars("Worksheet Menu Bar").FindControl( _ 
    ID:=1849, recursive:=True).Execute 
End Sub 
 
 
((Work With closed books)) 
Auto_Close() 
 
Dim intStatusState As Integer 
 
intStatusState = Application.DisplayStatusBar 
Application.DisplayStatusBar = True 
Application.StatusBar = "Examining transactions." 
DetermineTransactions 
Application.StatusBar = "Posting transactions." 
PostTransactions 
Application.StatusBar = False 
Application.DisplayStatusBar = intStatusState 
End Sub 
 
 
((Macros In Books)) 
Sub RunAutoOpenMacrosInBooks() 
    Dim J As Integer 
    Dim sTarget As String 
     
    Application.ScreenUpdating = False 
    For J = 1 To 999 
        sTarget = "Book" & Format(J, "000") & ".xls" 
        On Error Resume Next 
        Workbooks.Open sTarget 
        Windows(sTarget).Activate 
        With ActiveWorkbook 
            If .Name  ThisWorkbook.Name Then 
                .RunAutoMacros xlAutoOpen 
                .Save 
                .Close 
            End If 
        End With 
    Next i 
    Application.ScreenUpdating = True 
End Sub 
 
 
((Copy And paste from wsh To New book)) 
Sub Summarize() 
     
    Dim Counter As Integer 
    Dim Source As Workbook 
     
    Dim OriginalWorkBook As Workbook 
    Set OriginalWorkBook = ThisWorkbook 
    Const MyDir As String = "H:VickyexcelDave" 
     
    Application.ScreenUpdating = False 
     
     
    For Counter = 1 To 2 
         
        Set Source = Workbooks.Open(MyDir & "Book" & Counter & ".xls") 
        Source.Worksheets("Sheet1").Rows("1:1").Copy 
         'ERROR BEFORE IT GETS HERE
         
        OriginalWorkBook.Worksheets("Sheet1").Rows(Counter).Select 
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
        False, Transpose:=False 
         
        Source.Close False 
    Next 
     
     
    OriginalWorkBook.SaveAs MyDir & ThisWorkbook 
    Application.ScreenUpdating = True 
     
End Sub 

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


Hi Greetings,
I am stucked and I hope I can get some help from here.

What I am doing is workbook_open auto-fires the macro OpenMMOActivityReport which has dialogbox asking user to open the weekly updated xls. In this weekly workbook, insert column A, at A1 input Fieldname as "Notification Date", at A2 input function as "=getFilename()"

I tried to export the macro Module named "getFiledate" from "template.xls" to the weeky updated workbook "MMO Activity Report mm-dd-yy.xls" which user will open. The macro bugs at "MMO Activity Report 09-23-06" because every week the date changes to "MMO Activity Report 09-28-06", "MMO Activity Report 10-05-06", etc.

I am sure there is a way to code such that the macro can recognise the forever-changing filename of workbook "MMO Activity Report MMO mm-dd-yy" as perhaps "ActiveWindow.ActivatePrevious", or an alternative. Someone please help me. Many thanks in advance.


	VB:
	
 Workbook_Open() 
    If ActiveWorkbook.Name = "template.xls" Then 
        Call OpenMMOActivityReport 
    Else 
        Cancel = True 
    End If 
End Sub 

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


	VB:
	
 OpenMMOActivityReport() 
    Dim strDocument As String 
    strDocument = Application.GetOpenFilename("Microsoft All Excel Files,*.xls,All Files,*.*", 1, "Open File", , False) ' get
xls document name
    If Len(strDocument) < 6 Then Exit Sub 
    ActiveWorkbook.FollowHyperlink strDocument 
    Windows(1).Activate 
    Columns("A:A").Select 
    Selection.Insert Shift:=xlToRight 
    ActiveWindow.ActivatePrevious 
     
    Call CopyOneModule 
     
    Range("A1").Select 
    ActiveCell.FormulaR1C1 = "Notification Date" 
    Range("A2").Select 
    ActiveCell.FormulaR1C1 = "=getFiledate()" 
End Sub 

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


	VB:
	
 CopyOneModule() 
    Dim FName As String 
    With Workbooks("template") 
        FName = .Path & "code.txt" 
        .VBProject.VBComponents("getFiledate").Export FName 
    End With 
    Workbooks("MMO Activity Report 09-29-06").VBProject.VBComponents.Import FName 
End Sub 

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


	VB:
	
 Filedate() 
    Application.Volatile 
    Filename = Right(ActiveWorkbook.Name, 8) 'eg. gets mm-dd-yy of thr filename MMO Activity Report 19-09-06
    Filedate = Format(DateValue(Mid(Filename, 1, 2) & "-1-" & Mid(Filename, 4, 2) & "-1-" & Mid(Filename, 7, 2)),
"yyyy.mm.dd") 
End Function 

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


Hi,

I am having a workbook in server, it consists more than 12 sheets. Everyday in the early morning it need to be get updated, (i.e) normally when i open the file, it as for the Password (password for workbook), then after I given it will show three buttons Update Don't Update Help, so i need to press the Update Button , it should be done everyday in the early moring.

Is there any way to do this automatically open and update and close everday through vba code or anyother ways.

Need to be done everyday in the morning 6 AM

Thanks,
Chock.

I was directed to a VBS script to solve a problem that I had adding users to Active Directory based upon information in an Excel spreadsheet. The VBScript works great but I have never taken VBS and changed it to VBA. I would like to use this basic code and put it behind a command button to allow our HR staff to create the new users they have put on this spreadsheet. Any help I could be given would be much appreciated. I have included the VBScript code.


	VB:
	
 ' VBScript program to create users according to the information in a
 ' Microsoft Excel spreadsheet.
 '
 ' ----------------------------------------------------------------------
 ' Copyright (c) 2003 Richard L. Mueller
 ' Hilltop Lab web site - http://www.rlmueller.net
 ' Version 1.0 - September 8, 2003
 ' Version 1.1 - January 25, 2004 - Modify error trapping.
 ' Version 1.2 - March 18, 2004 - Modify NameTranslate constants.
 '
 ' You have a royalty-free right to use, modify, reproduce, and
 ' distribute this script file in any way you find useful, provided that
 ' you agree that the copyright owner above has no warranty, obligations,
 ' or liability for such use.
 
Option Explicit 
 
Dim objExcel, strExcelPath, objSheet 
Dim strLast, strFirst, strMiddle, strPW, intRow, intCol 
Dim strGroupDN, objUser, objGroup, objContainer 
Dim strCN, strNTName, strContainerDN 
Dim strHomeFolder, strHomeDrive, objFSO, objShell 
Dim intRunError, strNetBIOSDomain, strDNSDomain 
Dim objRootDSE, objTrans, strLogonScript, strUPN 
 
 ' Constants for the NameTranslate object.
Const ADS_NAME_INITTYPE_GC = 3 
Const ADS_NAME_TYPE_NT4 = 3 
Const ADS_NAME_TYPE_1779 = 1 
 
 ' Specify spreadsheet.
strExcelPath = "c:MyFolderNewUsers.xls" 
 
 ' Specify DN of container where users created.
strContainerDN = "ou=Sales,dc=MyDomain,dc=com" 
 
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objShell = CreateObject("Wscript.Shell") 
 
 ' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE") 
strDNSDomain = objRootDSE.Get("DefaultNamingContext") 
 
 ' Use the NameTranslate object to find the NetBIOS domain name
 ' from the DNS domain name.
Set objTrans = CreateObject("NameTranslate") 
objTrans.Init ADS_NAME_INITTYPE_GC, "" 
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain 
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4) 
 ' Remove trailing backslash.
strNetBIOSdomain = Left(strNetBIOSDomain, Len(strNetBIOSDomain) - 1) 
 
 ' Open spreadsheet.
Set objExcel = CreateObject("Excel.Application") 
 
On Error Resume Next 
objExcel.Workbooks.Open strExcelPath 
If Err.Number  0 Then 
    On Error Goto 0 
    Wscript.Echo "Unable to open spreadsheet " & strExcelPath 
    Wscript.Quit 
End If 
On Error Goto 0 
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) 
 
 ' Bind to container where users to be created.
On Error Resume Next 
Set objContainer = GetObject("LDAP://" & strContainerDN) 
If Err.Number  0 Then 
    On Error Goto 0 
    Wscript.Echo "Unable to bind to container: " & strContainerDN 
    Wscript.Quit 
End If 
On Error Goto 0 
 
 ' Start with row 2 of spreadsheet.
 ' Assume first row has column headings.
intRow = 2 
 
 ' Read each row of spreadsheet until a blank value
 ' encountered in column 5 (the column for cn).
 ' For each row, create user and set attribute values.
Do While objSheet.Cells(intRow, 5).Value  "" 
     ' Read values from spreadsheet for this user.
    strFirst = Trim(objSheet.Cells(intRow, 1).Value) 
    strMiddle = Trim(objSheet.Cells(intRow, 2).Value) 
    strLast = Trim(objSheet.Cells(intRow, 3).Value) 
    strPW = Trim(objSheet.Cells(intRow, 4).Value) 
    strCN = Trim(objSheet.Cells(intRow, 5).Value) 
    strNTName = Trim(objSheet.Cells(intRow, 6).Value) 
    strUPN = Trim(objSheet.Cells(intRow, 7).Value) 
    strHomeFolder = Trim(objSheet.Cells(intRow, 8).Value) 
    strHomeDrive = Trim(objSheet.Cells(intRow, 9).Value) 
    strLogonScript = Trim(objSheet.Cells(intRow, 10).Value) 
     ' Create user object.
    On Error Resume Next 
    Set objUser = objContainer.Create("user", "cn=" & strCN) 
    If Err.Number  0 Then 
        On Error Goto 0 
        Wscript.Echo "Unable to create user with cn: " & strCN 
    Else 
        On Error Goto 0 
         ' Assign mandatory attributes and save user object.
        If strNTName = "" Then 
            strNTName = strCN 
        End If 
        objUser.sAMAccountName = strNTName 
        On Error Resume Next 
        objUser.SetInfo 
        If Err.Number  0 Then 
            On Error Goto 0 
            Wscript.Echo "Unable to create user with NT name: " & strNTName 
        Else 
             ' Set password for user.
            objUser.SetPassword strPW 
            If Err.Number  0 Then 
                On Error Goto 0 
                Wscript.Echo "Unable to set password for user " & strNTName 
            End If 
            On Error Goto 0 
             ' Enable the user account.
            objUser.AccountDisabled = False 
            If strFirst  "" Then 
                objUser.givenName = strFirst 
            End If 
             ' Assign values to remaining attributes.
            If strMiddle  "" Then 
                objUser.initials = strMiddle 
            End If 
            If strLast  "" Then 
                objUser.sn = strLast 
            End If 
            If strUPN  "" Then 
                objUser.userPrincipalName = strUPN 
            End If 
            If strHomeDrive  "" Then 
                objUser.homeDrive = strHomeDrive 
            End If 
            If strHomeFolder  "" Then 
                objUser.homeDirectory = strHomeFolder 
            End If 
            If strLogonScript  "" Then 
                objUser.scriptPath = strLogonScript 
            End If 
             ' Set password expired. Must be changed on next logon.
            objUser.pwdLastSet = 0 
             ' Save changes.
            On Error Resume Next 
            objUser.SetInfo 
            If Err.Number  0 Then 
                On Error Goto 0 
                Wscript.Echo "Unable to set attributes for user with NT name: " _ 
                & strNTName 
            End If 
            On Error Goto 0 
             ' Create home folder.
            If strHomeFolder  "" Then 
                If Not objFSO.FolderExists(strHomeFolder) Then 
                    On Error Resume Next 
                    objFSO.CreateFolder strHomeFolder 
                    If Err.Number  0 Then 
                        On Error Goto 0 
                        Wscript.Echo "Unable to create home folder: " & strHomeFolder 
                    End If 
                    On Error Goto 0 
                End If 
                If objFSO.FolderExists(strHomeFolder) Then 
                     ' Assign user permission to home folder.
                    intRunError = objShell.Run("%COMSPEC% /c Echo Y| cacls " _ 
                    & strHomeFolder & " /T /E /C /G " & strNetBIOSDomain _ 
                    & "" & strNTName & ":F", 2, True) 
                    If intRunError  0 Then 
                        Wscript.Echo "Error assigning permissions for user " _ 
                        & strNTName & " to home folder " & strHomeFolder 
                    End If 
                End If 
            End If 
             ' Group DN's start in column 11.
            intCol = 11 
            Do While objSheet.Cells(intRow, intCol).Value  "" 
                strGroupDN = Trim(objSheet.Cells(intRow, intCol).Value) 
                On Error Resume Next 
                Set objGroup = GetObject("LDAP://" & strGroupDN) 
                If Err.Number  0 Then 
                    On Error Goto 0 
                    Wscript.Echo "Unable to bind to group " & strGroupDN 
                Else 
                    objGroup.Add objUser.AdsPath 
                    If Err.Number  0 Then 
                        On Error Goto 0 
                        Wscript.Echo "Unable to add user " & strNTName _ 
                        & " to group " & strGroupDN 
                    End If 
                End If 
                On Error Goto 0 
                 ' Increment to next group DN.
                intCol = intCol + 1 
            Loop 
        End If 
    End If 
     ' Increment to next user.
    intRow = intRow + 1 
Loop 
 
Wscript.Echo "Done" 
 
 ' Clean up.
objExcel.ActiveWorkbook.Close 
objExcel.Application.Quit 
Set objUser = Nothing 
Set objGroup = Nothing 
Set objContainer = Nothing 
Set objSheet = Nothing 
Set objExcel = Nothing 
Set objFSO = Nothing 
Set objShell = Nothing 
Set objTrans = Nothing 
Set objRootDSE = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you all so much in advance,
Ying&Yang

Hi there,

I havew written a simple VBA macro to open up a fixed width file into XLS. What i need to know is for one of the fields how would i specifically tell Excel to treat it like date/format? Currently my script looks like this :-

Workbooks.OpenText Filename:= x, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15 _
, 1), Array(24, 1), Array(33, 1), Array(41, 1), Array(57, 1), Array(73, 1), Array(81, 1), _
Array(83, 1), Array(84, 1), Array(87, 1), Array(91, 1) )

Any ideas ?

I would like to open an Excel Workbook and search for a specific text with VBA. Here is the problem:
I am in a Excel Workbook and I am looking for a specific text. Now I would like to make it possible to search from here in another workbooks on my system as well. This means that I need to be able to open a different Workbook, do the search and get the results of the search. I would like to know how to open a different workbook in VBA.

Thanks,

Dirk

Hi,

I track weekly timesheets on an excel spreadsheet and use VBA to create sheets from userforms, merge sheets to workbooks, and email to main office. The PC I was using stored files locally on hard drive and all was well for a few years. I have now been assigned a terminal server and the sheets/workbooks are now located on a network drive.

I edited the modules to reflect the new folder destinations but have troubles getting the files to save and merge from this network location. I find that the files do not save to the network drive but I find them in the default dir (My Documents) BTW I do have read/write permissions.

I have attached a bit of code where I seem to have the trouble. Please help?


	VB:
	
 Save_Sheet() 
     'Save time sheet with job name and number in filename
    Dim x 
    Dim wb As Workbook 
    Dim todate As String 
    Dim Foldername As String 
    jname = Range("B3") 
    jnumber = Range("H3") 
    todate = Format(Now, "mm-dd-yy") 
    Foldername = ("webservereTime" & todate) 
    Workbookname = ("webservereTime" & todate & ".xls") 
    Application.ScreenUpdating = False 
    On Error Resume Next 
    MkDir Foldername 
    On Error Goto 0 
    ChDir (Foldername) 
    ActiveSheet.Copy 
    Set wb = ActiveWorkbook 
    With wb 
        .SaveAs jname & " " & jnumber & ".xls", Password:="xxxx" 
    End With 
    ActiveWorkbook.Close 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The result from the code above places my timesheets in the default (My Documents) folder. I then copy the files from My Documents to the folder on the webserver machine, e: drive, Time folder.

Then I attempt to merge them with th following code:

	VB:
	
 sheetmrg() 
     'To merge weekly time sheets to one book for emailing
    Dim sBook As Workbook 
    Dim tbook As Workbook 
    Dim aSht As Worksheet 
    Dim Foldername As String 
    Dim todate As String 
    Application.ScreenUpdating = False 
    Set tbook = Workbooks.Add 
    todate = Format(Now, "mm-dd-yy") 
    Foldername = ("webservereTime" & todate & "") 
    fName = Dir(Foldername) 
    Do While (fName  "") 
        ChDir (Foldername) 
        Set sBook = Workbooks.Open(fName, , , , "xxxx") ' sheets pass protected
        For bb = 1 To sBook.Sheets.Count 
            Set aSht = sBook.Sheets(bb) 
            aSht.Copy after:=tbook.Sheets(tbook.Sheets.Count) 
            tbook.Sheets(tbook.Sheets.Count).Name = Range("H3") 
        Next bb 
        fName = Dir 
        sBook.Save 
        sBook.Close 
        ProgressDlg2.Show 
    Loop 
    Set xlBook = Nothing 
    Set xlSht = Nothing 
    Set tbook = Nothing 
    Set tbook = ActiveWorkbook 
    ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select 
    Application.DisplayAlerts = False 
    ActiveWindow.SelectedSheets.Delete 
    tbook.SaveAs ("webservereTime" & todate & ".xls") 
    tbook.Close 
    Application.ScreenUpdating = False 
    MsgBox "Merge Complete!" 
    UserForm5.Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here I get a runtime error 1004 cannot find "timesheet 10004.xls" when the sheet IS there. I discovered after numerous tries that a copy needs to be in My Documents as well for the merge to work. The tbook does save in the correct location.

I am stumped....when I change all locations to the local drive on my terminal server all works fine. Am I missing something??

Thanks so much for any help!!

Hi there, please help.

I am using Word VBA to import a range of data (may include chart) from Excel. In Word, I used Inlineshape object to act as a Excel object. Once the data is imported, user needs to be able to modify if they wish; however, the file is not linked to original file. I have problem importing the data into excel. The pastespecial method somehow does not work. Here are the code:

Sub Test7()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
On Error GoTo Err_Test
Application.ScreenUpdating = False
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName)
xlBook.Application.Visible = False
xlBook.Application.WindowState = xlMinimized

Dim InShape As InlineShape
Dim objXL As Object
Dim gXL As Excel.Worksheet

ActiveDocument.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.8", LinkToFile _
:=False, DisplayAsIcon:=False
Set InShape = ActiveDocument.InlineShapes(1)
InShape.Activate
Set objXL = InShape.OLEFormat.Object
Set gXL = objXL.ActiveSheet

' Method 1 succeed only when in debug mode
xlBook.Application.Range("A30:P88").Copy Destination:=gXL.Range("A1")

' Method 2 Fails completely
xlBook.Application.Range("C30:P88").Copy
gXL.PasteSpecial xlPasteValues
xlApp.CutCopyMode = False
SendKeys "{esc}", True

Exit_Test:
xlBook.Close False
xlApp.CutCopyMode = False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set InShape = Nothing
Set xlObj = Nothing
Application.ScreenUpdating = True
Exit Sub
Err_Test:
MsgBox Err.Description
GoTo Exit_Test

End Sub

I have method 1 and method 2, but none of them seems to work.

Thank you for your help.

Hello. I have two workbooks that run a macro on different intervals throughout the day. When only one workbook is open all works as planned, however, when I have two workbooks open running similar code, I get a debug error when the code tries to activate a sheet. I am not sure what is going on or if I am missing a statement. The code is as follows:


	VB:
	
 RunOnTime() 
    dTime = Now + TimeSerial(0, 15, 2) 
    Application.OnTime dTime, "RunOnTime" 
     
    lNum = lNum + 1 
    If lNum >= 35 Then 
        Run "CancelOnTime" 
    Else 
         
        ActiveWorkbook.Save 
        Sheets("webLB").Activate 
         
        Range("A9:N9").Select 
        Range(Selection, Selection.End(xlDown)).Select 
        ActiveWorkbook.Worksheets("webLB").Sort.SortFields.Clear 
        ActiveWorkbook.Worksheets("webLB").Sort.SortFields.Add Key:=Range("B9:B39"), _ 
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 
        With ActiveWorkbook.Worksheets("webLB").Sort 
            .SetRange Range("A9:N39") 
            .Header = xlGuess 
            .MatchCase = False 
            .Orientation = xlTopToBottom 
            .SortMethod = xlPinYin 
            .Apply 
        End With 
         
        Dim FTPcommand As String 
        Dim wsh As Object 
         
        FTPcommand = "ftp -n -s:" & Chr(34) & "C:DataExcelPoolsSurvivorFTP_commands.txt" & Chr(34) 
        Set wsh = CreateObject("WScript.Shell") 
        wsh.Run FTPcommand, 5, True 
    End If 
     
End Sub 

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


Hello folks

First post by a one-week-in user of VBA. Thanks to those questioners and contributors whose posts have helped me get to this point.

I have been developing code to update data in two workbooks and then transfer rows of data within and between them. I'm using Excel 2003.

My code is probably pretty clumsy, but it works in the two workbooks I wrote it in. Knowing the workbooks would be shared in the final environment I checked the restrictions on shared workbooks and, as far as I know, I haven't broken these.

The interface is all through user-forms with the bulk of the code running on a click of OK.

When I change to the workbook to Shared and start using it (still without any other versions running) I get run-time error messages. This happens whether I'm calling a macro that moves rows of data or one that simply updates cells an existing record and also happens when only one of the worklbooks is open and called to do an activity that does not reference the other.

Undoing the share option gets everything working normally again.

The two run-time error messages I've seen most are 1004 "Unable to set the MergeCells property of the Range class" and 9 "Subscript out of range". The only merged cells are on the top row of each worksheet (where the macro buttons are) and shouldn't be affected by any of the activities I'm running.

Do I need to revisit my code (about 10 routines) section by section or are there "known issues" and workarounds for macros in shared workbooks.

I haven't posted any code, because this seems to be an issue regardless of the macro called (and because my code is about as elegant as bull in a frock).

Thanks for reading this far,

Alan

I am sure that there is a way to do this but I don't know enough VBA to make this work. I would REALLY appreciate it if some kind soul out there would help me.

I work at a hotel, and every day I have to hand-write coupons for breakfast for each of our guests who arrive for certain local accounts that we have, and I need a separate coupon for each day they are staying. As you can imagine, this is very time consuming.

I had a similar situation a few months ago with guests who arrived and were part of our frequent stay program, and someone was kind enough to help me by writing a VBA code to run a macro to solve that problem. Unfortunately that macro will not work in this situation, as the report that I run from my system for this is not in the same format.

Since the people who will be using this spreadsheet and macro are my employees and may not know excel as well as I do, I need to do this in as few steps as possible. They are already familiar with importing data from another excel file and using Ctrl Q to run the macro. My coupon workbook is saved as password protected, with a prompt to open as read-only so that it can’t be messed up by someone by accident. (Password for attached file is password, no caps)

Currently we run a report from our reservation system, export it to excel, and save it to the desktop. Then we open the workbook that has the breakfast coupons in it. On the first tab they select import data and then select the saved excel file to import from. Then they go to the second tab in the coupons workbook and run the macro by using Ctrl Q. This creates the right number of coupons for each guest based on the number of days that they are staying with us.

In this first instance the report that I run has already sorted the guests, so it only includes the guests who I need coupons for. For this new set of coupons I need to not only account for the number of days a guest is staying, but for the coupons to be run only for certain guests based on the “code” that their reservation was booked under, as the report I can run from my system for this will not sort them first.

The report that I run from my system shows all arrivals for a given day or set of days. Each arrival will have a special code which shows what account they booked their reservation with.

I need a macro that will create coupons only for people fitting a certain set of codes. Also, I will need to be able to modify the list of included codes later in case we add any new codes or lose any current ones.

I have attached a copy of the files and reports that I am currently using.

The first file is the coupon workbook that I use. (Password is password, no caps) - Sorry I could not include this. Try as I might, I could not get it to fit the file size, not sure why. I will be happy to email this to someone who is interested!

The second file shows the report that we use for our frequent stay guests and import into the coupon workbook to make the coupons for them. (I show this just so you can see what already works) - Sorry, There is a maximum of 2 attachments per post. Again, I will be happy to email!

The third file is the report which I have which shows arrivals based on their booking “code”. This is listed in the far right column (the C-, L-, or S- before the 3 letter code can be ignored).

The fourth file is a list which shows what special rate codes include breakfast (all those which say BF). Guests who are listed in the third file, which shows arrivals based on their booking code, would need to be sorted using this information, to have coupons made for them.

Please note: All names have been changed and company names removed from these files for privacy purposes.

I know this is rather complicated, but I have attached all of the current files I have in the hopes that it will make things easier if someone chooses to help me.

Thank you in advance to anyone who has the chance to assist me with this!

Hi there, I am learning how to use excel with VBA.

I have a Userform with a textbox in it.
The user is asked to enter a serial number and press Enter.

When the Enter key is pressed, I would like to search for that serial nember in "SourceWorkbook.xls", Sheet1, in column A (or B).

If the Serial number is found it should be placed in "DestinationWorkbook.xls", Sheet1 Cell A1 etc. If no serial number is found I would like to display a message about it it.

The workbooks are already opened.

I am gratefull for any assistance.

Francesco Cembrola

I need a little VBA help here, i got the code listed below, this code "Creates a New Workbook" for each item listed in an excel table, then, copy all unique values in their respective Workbook and save it.

So, the problem is this...

...instead of creating a "New Workbook", i want to open a previously created Workbook (template) and do the procedures listed above. Is this posible?


	VB:
	
 Copy_To_Workbooks() 
    Dim CalcMode As Long 
    Dim ws1 As Worksheet 
    Dim ws2 As Worksheet 
    Dim WSNew As Worksheet 
    Dim rng As Range 
    Dim cell As Range 
    Dim Lrow As Long 
    Dim foldername As String 
    Dim MyPath As String 
    Dim FieldNum As Integer 
    Dim FileExtStr As String 
    Dim FileFormatNum As Long 
     
     'Name of the sheet with your data
    Set ws1 = Sheets("Sheet1") '

Hi,

I have a word template and have inserted bookmarks so I can export names from Excel into Word automatically.

I have the following code

	VB:
	
 Excel_to_Word() 
     
    Dim wdApp As Word.Application 
    Dim wdDoc As Word.Documents 
    Dim SheetRange As String 
    Dim BookMarkRange As String 
    Dim wb As Excel.Workbook 
    Dim xlName As Excel.Names 
    Set wb = ActiveWorkbook 
     
    Application.ScreenUpdating = False 
     'Application.StatusBar = "Uploading information to word "
    Application.DisplayStatusBar = True 
     
     'opens word document
    wdApp.Visible = True 
    Set wdDoc = wdApp.Documents.Add("K:2012 testtest.docx") 
     
     'Loop through names in the activeworkbook
    For Each xlName In wb.Names 
         'if xlName's name is existing in document then put the value in place of the bookmark
        If wdDoc.Bookmarks.Exists(xlName.Names) Then 
            wdDoc.Bookmarks(xlName.Names).Range.Text = Range(xlName.Value) 
        End If 
    Next xlName 
End With 

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


When I run the code, i get an error message: "compile error Method or data member not found". The code doesn't recognise "Bookmarks". I have checked the Microsoft Word 14.0 object library in the tools > references box.

Is there something wrong with the code? How do I get the code to recognise "bookmarks"?

Thanks.