Help to open same date files with different names

aj_hyd_vol1 · Jul 1st 2020

Hello all, Greetings...

I have finished macro coding to merge data from one file to another file. My problem lies here.

I have a hundreds of files in the folder

TAB01JUN2020.CSV

TAB02JUN2020.CSV

TAB03JUN2020.CSV

TAB04JUN2020.CSV

TAB05JUN2020.CSV .....so on

and the second set of files in the same folder

RTD01062020.csv

RTD02062020.csv

RTD03062020.csv

RTD04062020.csv

I want to open a file TAB01JUN2020.csv (this is master file), after that I have to open another file checking the same date as is the master file i.e., RTD01062020.csv and merge the data using Macro which is I already done.

My question is how to open same set of date files and do the macro work. If TAB01JUN2020.CSV opens then automatically it will check the same date and need to open the RTD01062020.csv file and do the macro job.

I request you to all please help in this regard.

Thanks with regards,

Babu

**royUK** · Jul 1st 2020

CSV files cannot contain macros. What exactly do you mean?

aj_hyd_vol1 · Jul 1st 2020

Yes. I have saved macro in a new work book. From that macro work book I need to open TAB01JUN2020.CSV then RTD01062020.csv. Then some data from RTD01062020.csv file need to be copied into the TAB01JUN2020.csv file. Like this I have so many files as I explained in the mail.

I have to work with these types of files. DDMMYYYY will be dynamic in each file name. When I choose to open TAB03JUN2020.csv then automatically it should open RTD03JUN2020.csv file.

I have a hundreds of files in the folder

TAB01JUN2020.CSV

TAB02JUN2020.CSV

TAB03JUN2020.CSV

TAB04JUN2020.CSV

TAB05JUN2020.CSV .....so on

and the second set of files in the same folder

RTD01062020.csv

RTD02062020.csv

RTD03062020.csv

RTD04062020.csv

Thanks with regards,

**royUK** · Jul 1st 2020

Can you attach what you have so far

aj_hyd_vol1 · Jul 1st 2020

Hi Roy,

Sorry for the typo error in the file extension in my earlier mail.

The second batch files I have given that file format is not *.csv, it is *.dat file. I'm importing thorugh Data > from text option.

**royUK** · Jul 1st 2020

So you need to loop through all the csv files in the folder that contain JUN and import some data. Where is the data?

Likewise with the dat files?

dangelor · Jul 1st 2020

Possibly...

Code

Sub Open_Same_Date_File()
    Dim sFile As String, sMonth As String

    sFile = Application.GetOpenFilename("CSV Files (*.csv),*.csv")
    If Right(sFile, 3) = "csv" Then
        Workbooks.Open (sFile)
        sFile = replace(sFile, "TAB", "RTD")
        sMonth = Mid(sFile, Len(sFile) - 10, 3)
        sFile = replace(sFile, sMonth, Format(CDate(sMonth & "/1 0"), "mm"))
        Workbooks.Open (sFile)
    End If
End Sub

Display More

dangelor · Jul 1st 2020

Added line to change to dat files

Code

Sub Open_Same_Date_File()
    Dim sFile As String, sMonth As String

    sFile = Application.GetOpenFilename("CSV Files (*.csv),*.csv")
    If Right(sFile, 3) = "csv" Then
        Workbooks.Open (sFile)
        sFile = replace(sFile, "TAB", "RTD")
        sMonth = Mid(sFile, Len(sFile) - 10, 3)
        sFile = replace(sFile, sMonth, Format(CDate(sMonth & "/1 0"), "mm"))
        sFile = replace(sFile, "csv", "dat")
        Workbooks.Open (sFile)
    End If
End Sub

Display More

aj_hyd_vol1 · Jul 2nd 2020

Hi Dangelor

Thank you very much for your code and for your time spent on this. When I paste the code in VBA I'm getting error code 1004. I'm not figure out what's the wrong on my side. I'm attaching source files and explaining what I need from those files. Please do the needful, it will be a very helpful to complete the process.

I have *.csv and *.DAT files in c:\1 directory. What I need is I have to open date-wise *.CSV file then again I have to open same date file of *.DAT extension file. In that *.DAT file I have Unit,1,2,3 columns. In the 3rd column numeric data should bring to *.CSV file 'B" column. For this I'm using manual operation using "vlookup". But that is time taking process to do the 100s of files.

I request you to please help me to do the above using Excel VBA.

Thanks with regards,

ABabu

aj_hyd_vol1 · Jul 2nd 2020

Sorry, File extension format not allowing to attach the files.

Hi Dangelor

Thank you very much for your code and for your time spent on this. When I paste the code in VBA I'm getting error code 1004. I'm not figure out what's the wrong on my side. I'm attaching source files and explaining what I need from those files. Please do the needful, it will be a very helpful to complete the process.

I have *.csv and *.DAT files in c:\1 directory. What I need is I have to open date-wise *.CSV file then again I have to open same date file of *.DAT extension file. In that *.DAT file I have Unit,1,2,3 columns. In the 3rd column numeric data should bring to *.CSV file 'B" column. For this I'm using manual operation using "vlookup". But that is time taking process to do the 100s of files. (For this I'm importing *.DAT file to excel using DATA > from text > comma delimiter > process.)

When I'm trying to attach the file it's not allowing to do. I'm pasting the data for your quick reference. In the *.CSV file data format is like this.

Column A	Column B
Unit
SRIKAKULAM
VISAKHAPATNM
RAJAHMUNDRY
TPGUDEM
VIJAYAWADA
MANGALAGIRI
KAKINADA
GUNTUR
ONGOLE
TIRUPATI
NELLORE
BANGALORE
CUDDAPAH
ANANTHAPUR
BANGALORE
CHENNAI
MUMBAI
DELHI
HYDERABAD
BENGAL

In *.DAT file data format is like this :

Unit,1,2,3

SRIKAKULAM,10500,2250,1050

VISAKHAPATNM,22500,1050,900

RAJAHMUNDRY,10500,2250,1050

TPGUDEM,10500,2250,1050

VIJAYAWADA,10500,2250,1050

MANGALAGIRI,10500,2250,1050

KAKINADA,10500,2250,1050

GUNTUR,10500,2250,1050

ONGOLE,10500,2250,1050

TIRUPATI,10500,2250,1050

NELLORE,10500,2250,1050

BANGALORE,10500,2250,1050

CUDDAPAH,10500,2250,1050

ANANTHAPUR,10500,2250,1050

BANGALORE,10500,2250,1050

CHENNAI,10500,2250,1050

MUMBAI,10500,2250,1050

DELHI,10500,2,1050

HYDERABAD,10500,0,1050

BENGAL,10500,2250,1050

I request you to please help me to do the above using Excel VBA.

Thanks with regards,

ABabu

dangelor · Jul 2nd 2020

Quote

When I paste the code in VBA I'm getting error code 1004.

ABabu,

On which line of code did you experience this error?

Help to open same date files with different names

Participate now!

Share

Tags