VBA CODE. Button to browse and copy files into a workbook.

[mergim]

Hello there,

I am trying to make a button that opens up the browser where I then can load an excel file, which then copies the data from the loaded file. The data I want to copy is marked in yellow on picture one. Here the range is A5:F16, however that sometimes changes, so I need some sort of a dynamic range.copy. Maybe you could say something like copy range from where a cell value in column a is = 1.currentregion, in this case cell A5.
The number of rows can vary, so I need some sort of code that copies the last line, in this case to F16. I also want the button to be able to load files endless times. Lets say I have loaded one file, which goes from A5:F16. The second time I load a file, it needs to start from the next empty row which, in this case, would be row A17.
The last thing that I hope is possible, is to add the text after "Design id.:", which in this case is "12345-12345-12345" (see A1) to the amount of rows there are, in this case from A5 to F17, so 12 rows on the left of "Process no" in column A.

I hope this makes sense and is possible.

Thanks in advance,

 

[RobP]

Hi Mergim,

I have a couple of questions to try and get some more clarity from you please.
You mention in the "data files" lets call them that you want to open, the range of data to be copied is not always the same. The rows, I understand can be variable - no problem. But does the starting (top left) cell always appear to be A5 (as per your example) ? Is it always starting in Column A (but the header info eg. rows 1-4) can vary ?, or does the data sometimes start in Column B5 for example ?

Then, the last request you had in copying the DEsign id.: reference number. I am not clear on where you want to put this 12345-12345-12345 number ? I don't understand what you mean by 12 rows on the left of Process no in column A. Do you mean all the data copied has to be shifted 12 cells to the right ?

thanks to clarify for us.

Rob

 

[mergim]

Hi Mergim,

I have a couple of questions to try and get some more clarity from you please.
You mention in the "data files" lets call them that you want to open, the range of data to be copied is not always the same. The rows, I understand can be variable - no problem. But does the starting (top left) cell always appear to be A5 (as per your example) ? Is it always starting in Column A (but the header info eg. rows 1-4) can vary ?, or does the data sometimes start in Column B5 for example ?

Then, the last request you had in copying the DEsign id.: reference number. I am not clear on where you want to put this 12345-12345-12345 number ? I don't understand what you mean by 12 rows on the left of Process no in column A. Do you mean all the data copied has to be shifted 12 cells to the right ?

thanks to clarify for us.

Rob

Hello Rob, thanks for reaching out


The starting top left can vary, so it does not always start from A5, it could start from A7 as well, however the data I want to copy always starts on column A. The header info can vary in terms of row, some times 5 rows, sometimes 4, in this case four. Having this variation automatically means that the data range that I want to copy changes, meaning that the range I want to copy not always starts from A5.

With regards to your second question, the text/number "12345-12345-12345" should be copied into the amount of rows of data there are (see picture). Here you have from row 5 to 16, which in total is 12 rows. For every single row I want the text/number to be copied to the left of "Process no".

Once again, thank you very much for reaching out. Let me know if there is any other question, or need of clarification

 

[RobP]

Thanks for the clarification. So the data always starts in Column A. I note your data is dummy data of course - but just grasping at straws here, does the data always start with the same Process number (eg. a "1" in your example, or can the first process number in each data file be totally different ?
Essentially (as my skills are limited) I'm looking for some kind of pattern I can latch onto in order to find your "top left" data cell, be that in row 5 or row 7 or row 24 .. Usually we could search for the "headers" above it - but I notice yours are blank in some cases. Does the header (the row above your data) always have the same titles ?

For the second point - got it, so data to be shifted one cell across.

thanks
Rob

 

[mergim]

Thanks for the clarification. So the data always starts in Column A. I note your data is dummy data of course - but just grasping at straws here, does the data always start with the same Process number (eg. a "1" in your example, or can the first process number in each data file be totally different ?
Essentially (as my skills are limited) I'm looking for some kind of pattern I can latch onto in order to find your "top left" data cell, be that in row 5 or row 7 or row 24 .. Usually we could search for the "headers" above it - but I notice yours are blank in some cases. Does the header (the row above your data) always have the same titles ?

For the second point - got it, so data to be shifted one cell across.

thanks
Rob

Hello

Yes, the process number always starts with 1.
Cell A1 always consists of the design number.
The headers e.g. in column A “process no” and “rm” etc. does not change, however the design number does change.
I hope it answers your questions.

 

[mergim]

Hello

Yes, the process number always starts with 1.
Cell A1 always consists of the design number.
The headers e.g. in column A “process no” and “rm” etc. does not change, however the design number does change.
I hope it answers your questions.

I could make a change in the file, so the design number has its own cell, so instead of having it all in A1, you could have the design number in A2

 

[RobP]

Hi Meigrim,

No need to make changes to your datafile. So in your main file (ie. the file you are copying all your data into), insert a VBA module (important) and put the below code into the module, rather than in the worksheet. Based on what you've told me, I assume in this main file, you also have your headers in row 1, starting with "Design ID" in A1, "Process No" in B1 ... etc. out to "Price" in G1. (as per your example, you also had some headers in Row2 - thats fine also. The actual data will start to be added from Row 3.

Draw a box somewhere on your main sheet (probably top, say in col H or I) and assign this macro to that button.

When the macro is run, its going to open up a dialogue box and ask you to load your excel datafile. Its going to operate on the data within, then close the file automatically. The data will be added to your main sheet with your design id's in column A. If we don't find a "1" in the first 20 rows of column A of your datafile, its going to quit, as it assumes its not a correct file.

Let me know how you get on..
cheers
Rob

Sub Main()
    
Dim intlastrow, datalastrow, datafirstrow As Integer
Dim master_array As Variant
Dim design_id As String
Dim my_message As String

my_message = "Import Data to this workbook .."

ControlFile = ThisWorkbook.Name

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:=my_message)
If NewFN = False Then
    ' User pressed Cancel
    Exit Sub
Else
    Workbooks.Open (NewFN)
    bookname = ActiveWorkbook.Name
        
    '**** Search for the first row of data in the datafile (bookname) thats been opened *****
    '*  first row of data in datafile in column A will contain a "1", so we search for that *
    
    For x = 1 To 20  ' set to 20, as if not found in first 20 rows, theres probably an issue somewhere
        If Cells(x, 1) = 1 Or Cells(x, 1) = "1" Then 'look for the first "1" in column A
            datafirstrow = x
            Exit For
        End If
    Next x
    If x = 20 Then
        MsgBox ("Data not found")
        Exit Sub
    End If
          
    '****************************************************************************************
               
        datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in the datafile
        design_id = Right(ActiveSheet.Cells(1, 1).Text, 17) 'obtain the Design_id number from "A1"
        
        master_array = Range(Cells(datafirstrow, 1), Cells(datalastrow, 6)) 'grab all the data into master_array storage
               
    ' close the datafile now we've used its data
    Workbooks(bookname).Saved = True 'make it think its saved already to avoid warning popup.
    Workbooks(bookname).Close   'close the data file without any warning msgs..
    
    'Store Data to main file below
    intlastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find the last row of data in the main file
    If intlastrow < 3 Then intlastrow = 2  'if no data in file, data will start from row 3 to allow first 2 rows of Header Titles
    ActiveSheet.Range(Cells(intlastrow + 1, 2), Cells(intlastrow + 1 + datalastrow - datafirstrow, 6)) = master_array 'dump the data onto the main sheet
    
    'store design id in column A
    ActiveSheet.Range(Cells(intlastrow + 1, 1), Cells(intlastrow + 1 + datalastrow - datafirstrow, 1)) = design_id
    
    Set master_array = Nothing  'clean memory
    
End If

End Sub

 

[mergim]

Hi Meigrim,

No need to make changes to your datafile. So in your main file (ie. the file you are copying all your data into), insert a VBA module (important) and put the below code into the module, rather than in the worksheet. Based on what you've told me, I assume in this main file, you also have your headers in row 1, starting with "Design ID" in A1, "Process No" in B1 ... etc. out to "Price" in G1. (as per your example, you also had some headers in Row2 - thats fine also. The actual data will start to be added from Row 3.

Draw a box somewhere on your main sheet (probably top, say in col H or I) and assign this macro to that button.

When the macro is run, its going to open up a dialogue box and ask you to load your excel datafile. Its going to operate on the data within, then close the file automatically. The data will be added to your main sheet with your design id's in column A. If we don't find a "1" in the first 20 rows of column A of your datafile, its going to quit, as it assumes its not a correct file.

Let me know how you get on..
cheers
Rob

Sub Main()
   
Dim intlastrow, datalastrow, datafirstrow As Integer
Dim master_array As Variant
Dim design_id As String
Dim my_message As String

my_message = "Import Data to this workbook .."

ControlFile = ThisWorkbook.Name

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:=my_message)
If NewFN = False Then
    ' User pressed Cancel
    Exit Sub
Else
    Workbooks.Open (NewFN)
    bookname = ActiveWorkbook.Name
       
    '**** Search for the first row of data in the datafile (bookname) thats been opened *****
    '*  first row of data in datafile in column A will contain a "1", so we search for that *
   
    For x = 1 To 20  ' set to 20, as if not found in first 20 rows, theres probably an issue somewhere
        If Cells(x, 1) = 1 Or Cells(x, 1) = "1" Then 'look for the first "1" in column A
            datafirstrow = x
            Exit For
        End If
    Next x
    If x = 20 Then
        MsgBox ("Data not found")
        Exit Sub
    End If
         
    '****************************************************************************************
              
        datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in the datafile
        design_id = Right(ActiveSheet.Cells(1, 1).Text, 17) 'obtain the Design_id number from "A1"
       
        master_array = Range(Cells(datafirstrow, 1), Cells(datalastrow, 6)) 'grab all the data into master_array storage
              
    ' close the datafile now we've used its data
    Workbooks(bookname).Saved = True 'make it think its saved already to avoid warning popup.
    Workbooks(bookname).Close   'close the data file without any warning msgs..
   
    'Store Data to main file below
    intlastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find the last row of data in the main file
    If intlastrow < 3 Then intlastrow = 2  'if no data in file, data will start from row 3 to allow first 2 rows of Header Titles
    ActiveSheet.Range(Cells(intlastrow + 1, 2), Cells(intlastrow + 1 + datalastrow - datafirstrow, 6)) = master_array 'dump the data onto the main sheet
   
    'store design id in column A
    ActiveSheet.Range(Cells(intlastrow + 1, 1), Cells(intlastrow + 1 + datalastrow - datafirstrow, 1)) = design_id
   
    Set master_array = Nothing  'clean memory
   
End If

End Sub

Hello Rob,

Thanks for a very nice coding, this is exactly how I wanted it. You have done a very good job, thank you so much.
I forgot to mention where the data needs to be copied into, I'm very sorry for this. Everything else works perfect.

Overview of destination.
Design id should be copied into column A (currently copied into column A)
Process no should be copied into column B (currently copied into column B)
Trade name should be copied into column D (currently copied into column C)
RM should be copied into column E (currently copied into column D)
Consumption should be copied into column H (currently copied into column E)
Waste should be copied into column I (currently copied into column F)
(See picture 2)

Another thing that I forgot to mention is in the column with RM numbers, normally you have the number e.g. 123456 + "." and then the revision number "01", so "123456.01" (see picture 1). Is it possible to separate them so you have RM number in column E and Revision number in Column F (see picture 2). You always have a dot separating them.

Let me know if anything is unclear.
Again, thank you so much for everything.


Picture 1

PICTURE 2

 

[mergim]

Hello Rob,

Thanks for a very nice coding, this is exactly how I wanted it. You have done a very good job, thank you so much.
I forgot to mention where the data needs to be copied into, I'm very sorry for this. Everything else works perfect.

Overview of destination.
Design id should be copied into column A (currently copied into column A)
Process no should be copied into column B (currently copied into column B)
Trade name should be copied into column D (currently copied into column C)
RM should be copied into column E (currently copied into column D)
Consumption should be copied into column H (currently copied into column E)
Waste should be copied into column I (currently copied into column F)
(See picture 2)

Another thing that I forgot to mention is in the column with RM numbers, normally you have the number e.g. 123456 + "." and then the revision number "01", so "123456.01" (see picture 1). Is it possible to separate them so you have RM number in column E and Revision number in Column F (see picture 2). You always have a dot separating them.

Let me know if anything is unclear.
Again, thank you so much for everything.


Picture 1
View attachment 68415


PICTURE 2
View attachment 68414

Hello Rob,

Please ignore the second request regarding seperating the RM number and Revision. I just found out that, in some case a comma "," is used instead of a dot ".". The only thing you should focus on is the destination. Sorry if this has cause any trouble.

 

[RobP]

Hi Mergrim,

thats okay - it happens quite a lot - but you learn from it, and you only do it once therefore.

Heres the new code (I've tweaked the original also a little). It should handle everything you want.

Good luck.
cheers
Rob

Sub Main()
    
Dim intlastrow, datalastrow, datafirstrow, newdatarows As Integer
Dim master_array As Variant
Dim design_id As String
Dim my_message As String

my_message = "Import Data to this workbook .."

ControlFile = ThisWorkbook.Name

NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:=my_message)
If NewFN = False Then
    ' User pressed Cancel
    Exit Sub
Else
    Workbooks.Open (NewFN)
    bookname = ActiveWorkbook.Name
        
    '**** Search for the first row of data in the datafile (bookname) thats been opened *****
    '*  first row of data in datafile in column A will contain a "1", so we search for that *
    
    For x = 1 To 20  ' set to 20, as if not found in first 20 rows, theres probably an issue somewhere
        If Cells(x, 1) = 1 Or Cells(x, 1) = "1" Then 'look for the first "1" in column A
            datafirstrow = x
            Exit For
        End If
    Next x
    If x = 20 Then
        MsgBox ("Data not found")
        Exit Sub
    End If
          
    '****************************************************************************************
               
        datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in the datafile
        newdatarows = datalastrow - datafirstrow + 1
        design_id = Right(ActiveSheet.Cells(1, 1).Text, 17) 'obtain the Design_id number from "A1"
        
        master_array = Range(Cells(datafirstrow, 1), Cells(datalastrow, 6)) 'grab all the data into master_array storage
               
    ' close the datafile now we've used its data
    Workbooks(bookname).Saved = True 'make it think its saved already to avoid warning popup.
    Workbooks(bookname).Close   'close the data file without any warning msgs..
    
    'Store Data to main file below
    intlastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find the last row of data in the main file
    If intlastrow < 3 Then intlastrow = 2  'if no data in file, data will start from row 3 to allow first 2 rows of Header Titles
    ActiveSheet.Range(Cells(intlastrow + 1, 2), Cells(intlastrow + newdatarows, 6)) = master_array   'dump the data onto the main sheet
    
    Range(Cells(intlastrow + 1, 5), Cells(intlastrow + newdatarows, 6)).Select
    Selection.Cut Destination:=Range(Cells(intlastrow + 1, 8), Cells(intlastrow + newdatarows, 9))
    Range(Cells(intlastrow + 1, 3), Cells(intlastrow + newdatarows, 4)).Select
    Selection.Cut Destination:=Range(Cells(intlastrow + 1, 4), Cells(intlastrow + newdatarows, 5))
    
    'store design id in column A
    ActiveSheet.Range(Cells(intlastrow + 1, 1), Cells(intlastrow + newdatarows, 1)) = design_id
    
    'split the RM  Revision number if there is 9 chars or more ie. a rev. no on the end of it
    For x = intlastrow + 1 To intlastrow + newdatarows
        If Len(CStr(Cells(x, 5))) > 8 Then
            Cells(x, 6) = Mid(Cells(x, 5), 10)
            Cells(x, 5) = Left(Cells(x, 5), 8)
        End If
    Next x
    
    Set master_array = Nothing  'clean memory
    
End If

End Sub