Modify Code

[josros60]

Hi

I have this code:

Sub ImportRawData()

    Dim c           As Long
    Dim Col         As Variant
    Dim Filename    As String
    Dim Filepath    As Variant
    Dim rngBeg      As Range
    Dim rngEnd      As Range
    Dim rngDst      As Range
    Dim rngSrc      As Range
    Dim rowsize     As Long
    Dim wkbDst      As Workbook
    Dim wkbSrc      As Workbook
    
        Set wkbDst = ThisWorkbook
        Set rngDst = wkbDst.Worksheets("EFT").Range("A4:E4")
        
        Filepath = "C:\Users\jose.rossi\Desktop\NCL EFT_Summary.xlsm"
        Filename = "apcbtclz.csv"
        
        On Error Resume Next
            Set wkbSrc = Workbooks(Filename)
            If Err = 9 Then
                If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
                Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
                If Filename = "False" Then Exit Sub
                Set wkbSrc = Workbooks.Open(Filename)
            End If
        On Error GoTo 0
        
        ' Clear previous data.
        rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents
        
        ' Import the data.
        With wkbSrc.Worksheets("apcbtclz").UsedRange
            ' Step through the source data columns.
            For Each Col In Array("AW", "BO", "BB", "AX", "X")
                ' Data starts on row 1.
                Set rngBeg = .Parent.Cells(1, Col)
                
                ' Find the row where the data ends in this column.
                Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
                
                ' Number of rows in this column.
                rowsize = rngEnd.Row '- rngBeg.Row
                
                If rowsize > 0 Then
                    Set rngSrc = .Parent.Range(rngBeg, rngEnd)
                    rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
                End If
                
                ' Increment the column offset.
                c = c + 1
            Next Col
        End With
        
End Sub

Question:

If possible in this line:

For Each Col In Array("AW", "BO", "BB", "AX", "X")

The

"AX"

column has the name of the director approving the invoice but if the row is blank how to modify the the code to add the word "Recurring" when blank.

Thank you,

 

[igold]

This is a lot of code for copying a five (5) of columns from one book to another. For instance if you know that the source data starts on row 1 why do you have to set that to a Range object. Your ranges are always going to start at AW1, BO1, BB1, etc. What kind of data is in your columns, are they values, formulas, or both. When you are clearing the data in UsedRange on the Destination Sheet, does that effectively clear all the data on the sheet or is there other data in some other areas that are not part of the used range. If "c' starts at zero then you are writing to columns A:E. Is it just those columns that you are clearing.

Maybe I am missing something but IMHO you sure are making this harder than it needs to be.

 

[josros60]

Hi,

Actually they are all values and just want column "AX" when is blank to enter the word Recurring.

to be hones I found this code online and no that good in VBA so just made the changes I know.
other problem I am having that this is a template and I have total formula but when the data is imported is deleting my total formula.

thank you,

 

[igold]

Except for the top half of the code that is checking for the correct Workbooks. If you could explain exactly what you want to accomplish (including the workbook names and sheet name), we could fix the code up to do what is required. If you could post some before and after data (real or fictitious) that would go a long way.

Also if you could update your profile information to reflect your Office Version and Platform, it would be a help to everyone who reads this and future posts.

 

[josros60]

Hi,

Here what I would like if possible any of the points below:

1. Inserting images of before/after and image of the data being imported from: both file that am using:

2. is it possible that change these vba lines no to check for the file name and sheet name:
With wkbSrc.Worksheets("apcbtclz").UsedRange

With wkbSrc.Worksheets("apcbtclz").UsedRange

(because the accounting software rename the file every time it prints it by adding a number at the end, to for example first print out file name will be " apcbtclz" but after will name it apcbtclz1, and so on. so in order the macros to work we need to save the the file and sheet name to this "apcbtclz" otherwise won't work.

3. the Column "AX" has the approval name of the director approving the invoice(s) to pay and I would like that when that field (row) is empty or blank to insert the word "Recurring".

4. In the template I have to import the data (by the way the file/sheet name never change) the problem having is that I have grand total formula at the end as you can see in the images but when data gets imported the formula will be deleted just wonder if there's a way formula will stay..

much appreciated your help.

is there anyway I can attach the files instead of images?

thank you,

 

[igold]

Download the XL2BB add-in found here. Change your profile as suggested.

https://www.mrexcel.com/board/excel-articles/xl2bb-excel-range-to-bbcode.1/download

 

[josros60]

Here the before mini sheet and after and also the file that importing from:

WONDERFUL CO. EFT_Summary.xlsm
	A	B	C	D	E	F
3	Sl #	Name	Amount	Approval	Payment batch	Remarks
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22		GRAND TOTAL =================>>	$ -
EFT

Cell Formulas
Range	Formula
C22	C22	=SUM(TOTAL)

Named Ranges
Name	Refers To	Cells
TOTAL	=EFT!$C$4:$C$21	C22

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=EFT

WONDERFUL CO. EFT_Summary.xlsm
	A	B	C	D	E	F
3	Sl #	Name	Amount	Approval	Payment batch	Remarks
4	1	Bell	$ 491.62	Luis Lopez	222
5	2	CANADA BOXING	$ 17,672.31	Regular service	222
6	3	Front Line	$ 6,412.75	Daniel Voug	222
7	4	IMPARK	$ 845.66	Walter Larus	222
8	5	LIVINGSTON INC	$ 239.18	Paul Smith	222
9	6	David Wong	$ 691.71	Walter Larus	222
10	7	Lawrence Leiu	$ 235.82	Roger Swift	222
11	8	Denis Seger	$ 701.50	Rob Switz/Kelly Sterling	222
12	9	MDM BUSINESS SOLUTIONS	$ 256.23	Rob Switz/Kelly Sterling	222
13	10	Mills Imported	$ 2,999.92	Shaun Ruiz/Juan Y/Susan Lopez	222
14	11	Pics	$ 23,399.83	Don Grand	222
15	12	UPS	$ 3,409.32	William Atz/Gerry S	222
16
17
18
19
20
21
22
EFT

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=EFT

apcbtclz.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ
1	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2800	No. of Checks Printed:	0		Entry No.:	1	Luis Lopez	Document No.:	PY04560	Payment Amt. (CAD):	491.62			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	BELL1691	Bell	Account Set:	APGEN
2	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2801	No. of Checks Printed:	0		Entry No.:	2	Regular service	Document No.:	PY04561	Payment Amt. (CAD):	17,672.31			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	CANPOST	CANADA BOXING	Account Set:	APGEN
3	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2802	No. of Checks Printed:	0		Entry No.:	3	Daniel Voug	Document No.:	PY04562	Payment Amt. (CAD):	6,412.75			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	FROLIN	Front Line	Account Set:	APGEN
4	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2803	No. of Checks Printed:	0		Entry No.:	4	Walter Larus	Document No.:	PY04563	Payment Amt. (CAD):	845.66			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	IMPARK	IMPARK	Account Set:	APGEN
5	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2804	No. of Checks Printed:	0		Entry No.:	5	Paul Smith	Document No.:	PY04564	Payment Amt. (CAD):	239.18			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	LIVING	LIVINGSTON INC	Account Set:	APGEN
6	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2805	No. of Checks Printed:	0		Entry No.:	6	Walter Larus	Document No.:	PY04565	Payment Amt. (CAD):	691.71			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	CHOOD	David Wong	Account Set:	APGEN
7	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2806	No. of Checks Printed:	0		Entry No.:	7	Roger Swift	Document No.:	PY04566	Payment Amt. (CAD):	235.82			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	ROBIL	Lawrence Leiu	Account Set:	APGEN
8	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2807	No. of Checks Printed:	0		Entry No.:	8	Rob Switz/Kelly Sterling	Document No.:	PY04567	Payment Amt. (CAD):	701.5			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	LEGER	Denis Seger	Account Set:	APGEN
9	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2808	No. of Checks Printed:	0		Entry No.:	9	Rob Switz/Kelly Sterling	Document No.:	PY04568	Payment Amt. (CAD):	256.23			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	MDM BU	MDM BUSINESS SOLUTIONS	Account Set:	APGEN
10	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2809	No. of Checks Printed:	0		Entry No.:	10	Shaun Ruiz/Juan Y/Susan Lopez	Document No.:	PY04569	Payment Amt. (CAD):	2,999.92			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	MICROS	Mills Imported	Account Set:	APGEN
11	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2810	No. of Checks Printed:	0		Entry No.:	11	Don Grand	Document No.:	PY04570	Payment Amt. (CAD):	23,399.83			Transaction Type:	Payment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	PICSCAD	Pics	Account Set:	APGEN
12	2021-07-30 6:37:14PM	wonderful company	Page 1	A/P Batch Listing - Payment (APCBTCLZ)	From Batch Number	[222] To [222]	From Batch Date	[2021-08-03] To [2021-08-03]	Type	[Entered, Imported, Generated, System, External]	Status	[Open, Ready To Post, Posted]	Reprint Previously Printed Batches	[Yes]			Show Tax Details	[Yes]	Show Adjustment Details	[Yes]	Show Optional Fields	[Yes]	Batch No.:	222	Description:	EFT Wonderful Company August 03, 2021 RA-JR	Currency:	CAD	Total Amount:	57,355.85	Batch Date:	8/3/2021	Type:	Entered	Source Application:	AP	No. of Entries:	12	Last Edited:	########	Status:	Open	Bank:	BMO2811	No. of Checks Printed:	0		Entry No.:	12	William Atz/Gerry S	Document No.:	PY04571	Payment Amt. (CAD):	3,409.32			Transaction Type:	Prepayment	Payment Date:	8/3/2021	Posting Date:	8/3/2021	Year - Period:	21-Aug	Vendor:	UPS CA	UPS	Account Set:	APGEN
apcbtclz

I also update the profile with office 365

thank you

 

[josros60]

Any help.....


thanks