Start Free Trial

Vinicius Teixeira

asked on

VBA - SaveCopyAs keeping XLSM format

Every where I`ve researched I just find how to save files in a different format, would someone be able to assist me to fix the following code?

   
    Dim ProjectNum As String
    ProjectNum = Range("B1405").Value
        'Set Path as a string
        
    Dim Path As String
    Path = Range("B1406").Value
        'Set Path as a string
           
    'Dim FileExtStr As String
    'FileExtStr = ".xlsm"
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    ActiveWorkbook.SaveCopyAs Filename:= _
        Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges

Open in new window

I want to save a new file whit out closing the matrix and keeping the same format as macro enabled (xlsm), It works fine when I simple SaveAs but when I run it as a SaveAsCopy I face the following error:

"Named argument not found"

Rey Obrero (Capricorn1)

check the value of string variable Path
and try this

ActiveWorkbook.SaveAs Filename:= _
Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I would recommend making a strFileName variable and displaying its value in the Immediate window using a Debug.Print statement so you can check that it is correct before using it in the SaveCopyAs expression.

Rey Obrero (Capricorn1)

the syntax for the SaveCopyAs is expression.SaveCopyAs(Filename)

ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & " - Global Task List" & ".xlsm"

Vinicius Teixeira

ASKER

Replacing the for the SaveAsCopy as below :

ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & " - Global Task List" & ".xlsm"

Open in new window

Run-Time error '1004':

I`m currently running this one by one trough a manual bottom

 

Sub GTL()
'
' GTL Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
'Application.Wait (Now + TimeValue("0:00:02"))
'Windows("Exception Letters builder@ 3.xlsm").Activate

Do
fRow = 3

    Workbooks.Open Filename:= _
    "H:\e055780\1.CIS\2. CIS Mandates\Docs\Audit docs\Global Task List Template.xlsm"
        'Open GTL
    
    'Windows("Global Task List Template.xlsm").Activate
    'Sheets("Project scoping").Select
    'Range("D19:D25").Select
    'Selection.ClearContents
        'Delete GTL Template
            
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Select Project# from base
        
    Windows("Global Task List Template.xlsm").Activate
    Range("D20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Project# into GTL
        
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        ' Select Project# from base
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("B1405").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Project# into GTL
        
            
    Range("D1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Select Customer name from base
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("Project scoping").Select
    Range("D21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Customer into GTL
        
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
               
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.Copy
        'Copy Today() Date
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste today as Project Start Date
   
        
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("K1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Copy GTL Path
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("B1406").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Path into GTL
        
    Dim ProjectNum As String
    ProjectNum = Range("B1405").Value
        'Set Path as a string
        
    Dim Path As String
    Path = Range("B1406").Value
        'Set Path as a string
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("K1").Select
    Selection.End(xlDown).Select
    Selection.ClearContents
        'Delete Path
        
    
     ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & " - Global Task List" & ".xlsm"
     
        
    'Windows("Global Task List Template.xlsm").Activate
    'Sheets("AllTasksMatrix").Select
    'ActiveWorkbook.SaveAs Filename:= _
        'Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
        'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'SavingAs
            
   Application.DisplayAlerts = False
   ActiveWorkbook.Close SaveChanges:=False

    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.ClearContents
        'Delete Project # from base
     
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("D1").Select
    ActiveCell.End(xlDown).Select
    Selection.ClearContents
        'Delete Customer
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("D1").Select
    ActiveCell.End(xlDown).Select
        'Reurn so does not break
               
    Loop Until IsEmpty(Cells(fRow, 1))
    
       
End Sub

'Deletes:
'GTL Content D20 to D25
'ProjectNum from Collum "C"
'CustomerNa from Collum "D"
'Path from Collum "K"



'Not able to save as afile
    
    'Windows("Global Task List Template.xlsm").Activate
    'Sheets("AllTasksMatrix").Select
    'ActiveWorkbook.SaveCopyAs Filename:= _
        'Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
        'CreateBackup:=False
        ', _
        FileFormat:=xlNormal
        'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'Saving

Open in new window

Sorry, we couldn't find the file:// (filename). Is it possible it was moved, renamed or deleted?

Did you try writing the whole file name to the Immediate window?

Vinicius Teixeira

ASKER

This regards a batch of 85 files I'm trying to save, I'm not familiarized with Immediate window, will check for this, appreciate all support so far.

There is a space in the hard-coded file name -- maybe a typo?

Vinicius Teixeira

ASKER

Sorry for reopening that but for some reason still getting the same error, when testing on Immediate Window gets the same message some issue with path, what stranges me is that when saving as there is no error

Try copying the path and file name from the folder, and compare it character by character with the string created in code.

Rey Obrero (Capricorn1)

:-O ????

Is 2. CIS correct , or should the space be removed?

Vinicius Teixeira

ASKER

yes inputs are fine , there is some issue on my output, when running a SaveCopyAs

  'Workbooks.Open Filename:= _
    '"H:\e055780\1.CIS\2. CIS Mandates\Docs\Audit docs\Global Task List Template.xlsm"
        'Open GTL

Open in new window

is disabled this is a turn around for manual run.

Are you trying to open the copied template or make a new workbook from it?

Vinicius Teixeira

ASKER

Make a new one from it. and save with a variable name.

The following is totally functionl but does not goes in loop :

Sub GTL()
'
' GTL Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
'Application.Wait (Now + TimeValue("0:00:02"))
'Windows("Exception Letters builder@ 3.xlsm").Activate

Do
fRow = 3

Application.DisplayAlerts = False

    Workbooks.Open Filename:= _
    "H:\e055780\1.CIS\2. CIS Mandates\Docs\Audit docs\Global Task List Template.xlsm"
        'Open GTL
    
    Windows("Global Task List Template.xlsm").Activate
    Sheets("Project scoping").Select
    Range("D19:D25").Select
    Selection.ClearContents
        'Delete GTL Template
            
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Select Project# from base
        
    Windows("Global Task List Template.xlsm").Activate
    Range("D20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Project# into GTL
        
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        ' Select Project# from base
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("B1405").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Project# into GTL
            
    Range("D1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Select Customer name from base
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("Project scoping").Select
    Range("D21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Customer into GTL
                      
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.Copy
        'Copy Today() Date
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste today as Project Start Date
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("K1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Copy GTL Path
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("B1406").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Path into GTL
        
    Dim ProjectNum As String
    ProjectNum = Range("B1405").Value
        'Set Path as a string
        
    Dim Path As String
    Path = Range("B1406").Value
        'Set Path as a string
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("K1").Select
    Selection.End(xlDown).Select
    Selection.ClearContents
        'Delete Path
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    ActiveWorkbook.SaveAs Filename:= _
        Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'SavingAs
    
    ActiveWorkbook.Close SaveChanges:=False
        
    'ActiveWorkbook.SaveCopyAs Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"
     
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.ClearContents
        'Delete Project # from base
     
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("D1").Select
    ActiveCell.End(xlDown).Select
    Selection.ClearContents
        'Delete Customer
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("D1").Select
    ActiveCell.End(xlDown).Select
        'Reurn so does not break
               
    Loop Until IsEmpty(Cells(fRow, 1))
    
       
End Sub

Open in new window

Rey Obrero (Capricorn1)

this is not correct
ActiveWorkbook.SaveCopyAs Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"

it should be

ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & "- Global Task List" & ".xlsm"

I don't see a Loop structure. The code is too long to work with on my phone -- maybe tomorrow on my desktop

Vinicius Teixeira

ASKER

Already changed it Rey, but without success.... this was my initial tough when I thought it was fine.

Rey Obrero (Capricorn1)

what are the values in Range("B1405") and Range("B1406")? , copy and paste them here

We could see them if you assigned the whole file path to a variable and displayed it in the Immediate window.

Vinicius Teixeira

ASKER

b10405 = CIS-2017-02004
bi0406 = file://bruutil06/ctos$/Projects/External Projects/USA/MetaBank/CIS-2017-02004/Implementation Documents

Rey Obrero (Capricorn1)

do this
Path = Range("B1406").Value
Path=Replace(Path,"file:","")

then use
ActiveWorkbook.SaveCopyAs Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"

Vinicius Teixeira

ASKER

I`ve replaced as ?

Dim Path As String
Path = Range("B1406").Value
Path = Replace(Path, "file:", "")
'Path = Range("B1406").Value
'Set Path as a string

and got samet error but associated to name as well, it`s a network folder do you think in may impact?

debug stills stoping @

ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & "- Global Task List" & ".xlsm"

It's getting late for me -- if the question is still open tomorrow I will return to it then.

Rey Obrero (Capricorn1)

Dim Path As String
Path = Range("B1406").Value
Path = Trim(Replace(Path, "file:", ""))

then use this

ActiveWorkbook.SaveCopyAs Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"

Vinicius Teixeira

ASKER

nop, would u be able to assist me to add a loop on it ?

My loop is not working, if so I can use the saveas then latter I check for the saveascopy

Rey Obrero (Capricorn1)

one more, run this

Dim Path As String
Path = Range("B1406").Value
Path = Trim(Replace(Path, "file:", ""))

debug.print Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"

after running that codes, look in the Immediate window for what was printed, copy and paste here
to open Immediate Window, from Menu > View >Immediate Window

Vinicius Teixeira

ASKER

gived me the following:

//bruutil06/ctos$/Projects/External Projects/USA/FISERV Solutions, LLC/CIS-2017-02003/Implementation Documents/CIS-2017-02001- Global Task List.xlsm

Vinicius Teixeira

ASKER

I was thinking on changing the name in the origin so I may have the full name, @ b1406 and I can invoke path directly but didn't worked as well, saw some people talking about filleformat = 54 or 52 but didn't worked as well

Rey Obrero (Capricorn1)

copy this

//bruutil06/ctos$/Projects/External Projects/USA/FISERV Solutions, LLC/CIS-2017-02003/Implementation Documents

and paste in URL of the windows explorer, see if you can open the folder or if the folder exists

also, get a snapshot of the error you are getting for error 1004

Vinicius Teixeira

ASKER

Only adding file: in front of it otherwise does not exist, it browses IE instead of Explorer

Vinicius Teixeira

ASKER

User generated image

Rey Obrero (Capricorn1)

my first post, I ask to check for the path ("check the value of string variable Path")

you can not save to a folder that does NOT exists.

Vinicius Teixeira

ASKER

path is fine this folder already exist and is empty :/

Rey Obrero (Capricorn1)

<path is fine this folder already exist and is empty :/ >

copy the path from windows explorer and paste here

Vinicius Teixeira

ASKER

I`ve composed the path name into the DB now and have the following when checking this at Immediate view:

file://bruutil06/ctos$/Projects/External Projects/USA/FISERV Solutions, LLC/CIS-2017-02991/Implementation Documents/CIS-2017-02991-Global Task List.xlsm

however have the same error, but if I take the CIS* off this is accessible - (CIS-2017-02991-Global Task List.xlsm
) is the name I`m trying to give to this new file

Rey Obrero (Capricorn1)

<path is fine this folder already exist and is empty :/ >

copy the path from windows explorer and paste here

Vinicius Teixeira

ASKER

sorry didn't got it earlier, here it is:

\\bruutil06\ctos$\Projects\External Projects\USA\FISERV Solutions, LLC\CIS-2017-02991\Implementation Documents

Rey Obrero (Capricorn1)

test this

dim Path as string
Path="\\bruutil06\ctos$\Projects\External Projects\USA\FISERV Solutions, LLC\CIS-2017-02991\Implementation Documents"

ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & "- Global Task List" & ".xlsm"

Vinicius Teixeira

ASKER

That`s what I have active now:

Sub GTL()
'
' GTL Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
'Application.Wait (Now + TimeValue("0:00:02"))
'Windows("Exception Letters builder@ 3.xlsm").Activate
'START

Do
fRow = 3

Application.DisplayAlerts = False

    'Workbooks.Open Filename:= _
    '"H:\e055780\1.CIS\2. CIS Mandates\Docs\Audit docs\Global Task List Template.xlsm"
        'Open GTL
    
    Windows("Global Task List Template.xlsm").Activate
    Sheets("Project scoping").Select
    Range("D19:D25").Select
    Selection.ClearContents
        'Delete GTL Template
            
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Select Project# from base
        
    Windows("Global Task List Template.xlsm").Activate
    Range("D20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Project# into GTL
        
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        ' Select Project# from base
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("B1405").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Project# into GTL
            
    Range("D1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Select Customer name from base
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("Project scoping").Select
    Range("D21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Customer into GTL
                      
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.Copy
        'Copy Today() Date
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste today as Project Start Date
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("K1").Select
    Selection.End(xlDown).Select
    Selection.Copy
        'Copy GTL Path
        
    Windows("Global Task List Template.xlsm").Activate
    Sheets("AllTasksMatrix").Select
    Range("B1406").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Paste Path into GTL
        
    Dim ProjectNum As String
    ProjectNum = Range("B1405").Value
        'Set ProjectNum as a string
        
    Dim Path As String
       Path = Range("B1406").Value
       'Path = Trim(Replace(Path, "file:", ""))
        
    'Dim Path As String
     ' Path = Range("B1406").Value
      'Path = Trim(Replace(Path, "file:", ""))
        
    'Dim Path As String
        'Path = Range("B1406").Value
        'Set Path as a string
           
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("K1").Select
    Selection.End(xlDown).Select
    Selection.ClearContents
        'Delete Path
        
    'Windows("Global Task List Template.xlsm").Activate
    'Sheets("AllTasksMatrix").Select
    'ActiveWorkbook.SaveAs Filename:= _
    '    Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
    '   FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'SavingAs
    
    ActiveWorkbook.SaveCopyAs Path
   
'Debug.Print Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"

'ActiveWorkbook.SaveCopyAs Path
        
        
'ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & "- Global Task List" & ".xlsm"
'ActiveWorkbook.SaveCopyAs Path & "/" & ProjectNum & "- Global Task List" & ".xlsm"
     
        
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("C1").Select
    Selection.End(xlDown).Select
    Selection.ClearContents
        'Delete Project # from base
        
    Loop Until IsEmpty(Cells(fRow, 1))
     
    Windows("Exception Letters builder@ 3.xlsm").Activate
    Range("D1").Select
    ActiveCell.End(xlDown).Select
    Selection.ClearContents
        'Delete Customer
           
    Windows("Global Task List Template.xlsm").Activate
    Range("D1").Select
    ActiveCell.End(xlDown).Select
        'Close new GTL
        
    ActiveWorkbook.Close SaveChanges:=False
               
  
       
End Sub

'Deletes:
'GTL Content D20 to D25
'ProjectNum from Collum "C"
'CustomerNa from Collum "D"
'Path from Collum "K"



'Not able to save as afile
    
    'Windows("Global Task List Template.xlsm").Activate
    'Sheets("AllTasksMatrix").Select
    'ActiveWorkbook.SaveCopyAs Filename:= _
        'Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
        'CreateBackup:=False
        ', _
        FileFormat:=xlNormal
        'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'Saving

    
    'Windows("Global Task List Template.xlsm").Activate
    'Sheets("AllTasksMatrix").Select
    'ActiveWorkbook.SaveAs Filename:= _
        'Path & "\" & ProjectNum & " - Global Task List" & ".xlsm", _
        'FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'SavingAs
            
   'Application.DisplayAlerts = False
   'ActiveWorkbook.Close SaveChanges:=False

Open in new window

Vinicius Teixeira

ASKER

Idea is that I have a list of multiple names the ''Exeption Letter'' db, a matrix (GTL) and would run a macro to save a copy of this matrix with all records name into the db

SavingAs have worked but as I told earlier it take some time manually activating the macro after each single loop and additionally I have to open that matrix what is a heavy file.

Rey Obrero (Capricorn1)

look at the image of the error 1004, you have at the end two .xlsm file

Vinicius Teixeira

ASKER

yes I fixed it

User generated image

Rey Obrero (Capricorn1)

test this and see if you can save a copy

dim Path as string
Path="\\bruutil06\ctos$\Projects\External Projects\USA\FISERV Solutions, LLC\CIS-2017-02991\Implementation Documents"

ActiveWorkbook.SaveCopyAs Path & "\" & ProjectNum & "- Global Task List" & ".xlsm"

Vinicius Teixeira

ASKER

OW my comment did not went trough , it worked, do you have in mind somehow to make it work for variable path names

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vinicius Teixeira

ASKER

I`m now trying to add a loop until IsEmpty

I`ve started the macro with DO and closed with the following statement

fRow = 3 - First line into my DB
Loop Until IsEmpty(Cells(fRow, 1)) until first line into the DB is empty,

Vinicius Teixeira

ASKER

Below worked:

Do Until Windows("Exception Letters builder@ 3.xlsm").Activate
    IsEmpty (Range("C1"))

Open in new window

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Here is a basic Loop structure:

Public Sub TestLoop()
'Created by Helen Feddema 5-Mar-2017
'Last modified by Helen Feddema 5-Mar-2017

On Error GoTo ErrorHandler

   Dim lngRow As Long
   Dim lngRows As Long
   Dim lngSetNo As Long
   Dim rng As Excel.Range
   Dim rngSet As Excel.Range
   Dim varValue As Variant
   Dim wks As Excel.Worksheet
   
   'Get last row number
   Set wks = Application.ActiveSheet
   lngRows = wks.UsedRange.Rows.Count
   Debug.Print "Number of data rows in worksheet: " & lngRows
   
   'Go to first cell
   Set rngSet = wks.Range("A1")
   
   'Set up loop to do something, using a value from the sheet
   Do Until lngRow >= lngRows
      Set rngSet = Application.ActiveCell.Offset(rowoffset:=1)
      rngSet.Select
      lngRow = rngSet.Row
      Debug.Print "Row number: " & lngRow
      varValue = rngSet.Value
      Debug.Print "Current cell value: " & varValue
      
      'Check whether the cell is empty, and move down
      'a row if it is (this is optional)
      Do While IsEmpty(varValue) = True
         Set rng = Application.ActiveCell.Offset(rowoffset:=1)
         rng.Select
         varValue = rng.Value
         Debug.Print "Current cell value: " & varValue
      Loop
      
      'Do something with the value
      MsgBox varValue
   Loop
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in TestLoop procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Vinicius Teixeira

ASKER

Thank you both for you time and engagement in this issue I've Learned a lot on this.