Need help with a Binary Excel file

[Bryan1992345]

I have a .xlsb file that doesn't create a .xlsx file and save it correctly. The .xlsb file is supposed to access every .csv within a known directory, pull the csv string into a row that is supposed to split into columns, then close the .csv file and loop through to the next .csv file in the directory. Once the loop ends, I would like for the .xlsb file to convert the formula and split text into actual text and remain within the columns they were intended to populate. The final process flow is to then have the .xlsb file create a new workbook (.xlsx), copy the sheet that the text data has been populated into to the new workbook, and save the new workbook as a set name.

 

I can get it to loop through all .csv. I, at one point, had it able to save the workbook - broken now for some reason. But I have not been able to get it to appropriately split the text while also converting the formula to text.

I need help; my beginner level Excel skills, I fear, are finally getting the better of me.

ZundPostOperationExport.xlsb

[Avocado Diaboli]

Am I right in assuming that the data in those CSV files is a single line of values? If that's the case, I'd suggest splitting it into an array in your VBA code, instead of relying on formulas to do the splitting. Also, I don't see a reason why you're trying to import the data into this file and save it as an .xlsx file. The easier way is to just create an .xltx template that has all the column headers populated, add a new file based on that template, aggregate the data from those CSV files into that new workbook and save that as an .xlsx. Something like this:

 

Sub Import_CSV_Files()

    'Turns off drawing the file contents to the screen, speeding up the process
    Application.ScreenUpdating = False
    
    'Create a new file to aggregate the contents of the CSV files
    'First you need to create an Excel template file that contains the desired column headers
    Dim targetFile As Workbook
    Set targetFile = Workbooks.Add("C:\Example\template.xltx")
    
    'Folder where all your CSV files are located
    Dim filePath As String
    filePath = "C:\Example\CSV files\"
    
    'Fetching the first file in the folder specified above
    Dim fileName As String
    fileName = Dir(filePath)
    
    'Loops through all the files in the folder specified above
    Do While Len(fileName) > 0
        
        Dim csvFile As Workbook
        Set csvFile = Workbooks.Open(filePath & fileName)
        
        'Load the data stored in the first cell into an array
        'Assumes the data is separated by ;
        'If not, change the delimiter to whatever type your files use
        Dim data As Variant
        data = Split(csvFile.Sheets(1).Range("A1").Value, ";")
    
        csvFile.Close SaveChanges:=False
        
        'Define the range in the target file where the stored data should be copied into.
        'Range("A1048576") is the last possible row of an Excel sheet
        'End(xlUp) jumps up from the last possible row to the last one filled with data
        'Offset(1) moves one row below, to the first empty row in the file
        'Resize(1, Ubound(data) + 1) expands the range to the number of columns determined by the copied data in the array
        targetFile.Sheets(1).Range("A1048576").End(xlUp).Offset(1).Resize(1, UBound(data) + 1).Value = data
        
        'Fetch the next file in the folder
        fileName = Dir
        
    Loop
    
    targetFile.SaveAs fileName:="C:\Example\Output.xlsx", FileFormat:=xlOpenXMLWorkbook
    targetFile.Close
    
    Application.ScreenUpdating = True

End Sub

 

Also, on a more general note, when you write these routines, get into the habit of declaring your variables where you need them. You tend to have declaration blocks right at the top for everything, which, coupled with the terse names of your variables, makes it hard to understand what types they are and why you've used them. Especially since you rely too much on the object datatype, instead of using more appropriate ones. Like when you want to refer to a workbook, define the varible as a workbook. That way you also get the autocomplete feature for the methods of that type. 

[Bryan1992345]

On 5/4/2024 at 2:25 AM, Avocado Diaboli said:

Am I right in assuming that the data in those CSV files is a single line of values? If that's the case, I'd suggest splitting it into an array in your VBA code, instead of relying on formulas to do the splitting. Also, I don't see a reason why you're trying to import the data into this file and save it as an .xlsx file. The easier way is to just create an .xltx template that has all the column headers populated, add a new file based on that template, aggregate the data from those CSV files into that new workbook and save that as an .xlsx. Something like this:

 

Sub Import_CSV_Files()

    'Turns off drawing the file contents to the screen, speeding up the process
    Application.ScreenUpdating = False
    
    'Create a new file to aggregate the contents of the CSV files
    'First you need to create an Excel template file that contains the desired column headers
    Dim targetFile As Workbook
    Set targetFile = Workbooks.Add("C:\Example\template.xltx")
    
    'Folder where all your CSV files are located
    Dim filePath As String
    filePath = "C:\Example\CSV files\"
    
    'Fetching the first file in the folder specified above
    Dim fileName As String
    fileName = Dir(filePath)
    
    'Loops through all the files in the folder specified above
    Do While Len(fileName) > 0
        
        Dim csvFile As Workbook
        Set csvFile = Workbooks.Open(filePath & fileName)
        
        'Load the data stored in the first cell into an array
        'Assumes the data is separated by ;
        'If not, change the delimiter to whatever type your files use
        Dim data As Variant
        data = Split(csvFile.Sheets(1).Range("A1").Value, ";")
    
        csvFile.Close SaveChanges:=False
        
        'Define the range in the target file where the stored data should be copied into.
        'Range("A1048576") is the last possible row of an Excel sheet
        'End(xlUp) jumps up from the last possible row to the last one filled with data
        'Offset(1) moves one row below, to the first empty row in the file
        'Resize(1, Ubound(data) + 1) expands the range to the number of columns determined by the copied data in the array
        targetFile.Sheets(1).Range("A1048576").End(xlUp).Offset(1).Resize(1, UBound(data) + 1).Value = data
        
        'Fetch the next file in the folder
        fileName = Dir
        
    Loop
    
    targetFile.SaveAs fileName:="C:\Example\Output.xlsx", FileFormat:=xlOpenXMLWorkbook
    targetFile.Close
    
    Application.ScreenUpdating = True

End Sub

 

Also, on a more general note, when you write these routines, get into the habit of declaring your variables where you need them. You tend to have declaration blocks right at the top for everything, which, coupled with the terse names of your variables, makes it hard to understand what types they are and why you've used them. Especially since you rely too much on the object datatype, instead of using more appropriate ones. Like when you want to refer to a workbook, define the varible as a workbook. That way you also get the autocomplete feature for the methods of that type. 

This is exactly what I was needing. This runs so much better. I honestly don't even know what declaring is or even how to properly format vba. Alot of what I "know" how to do is from copying and pasting from other projects that I found in varying forums, so I basically know nothing except how to smash things together and brute-force it to at least get me close to what I am trying to achieve. Looking at your solution, it reads cleanly (especially with how you have everything commented out - big thanks). I can hold my own in Python, but for whatever reason, I cannot visualize vba inside Excel. I'm working within the limits of my company, though, so no Python.

[Bryan1992345]

On 5/4/2024 at 2:25 AM, Avocado Diaboli said:

Am I right in assuming that the data in those CSV files is a single line of values? If that's the case, I'd suggest splitting it into an array in your VBA code, instead of relying on formulas to do the splitting. Also, I don't see a reason why you're trying to import the data into this file and save it as an .xlsx file. The easier way is to just create an .xltx template that has all the column headers populated, add a new file based on that template, aggregate the data from those CSV files into that new workbook and save that as an .xlsx. Something like this:

 

Sub Import_CSV_Files()

    'Turns off drawing the file contents to the screen, speeding up the process
    Application.ScreenUpdating = False
    
    'Create a new file to aggregate the contents of the CSV files
    'First you need to create an Excel template file that contains the desired column headers
    Dim targetFile As Workbook
    Set targetFile = Workbooks.Add("C:\Example\template.xltx")
    
    'Folder where all your CSV files are located
    Dim filePath As String
    filePath = "C:\Example\CSV files\"
    
    'Fetching the first file in the folder specified above
    Dim fileName As String
    fileName = Dir(filePath)
    
    'Loops through all the files in the folder specified above
    Do While Len(fileName) > 0
        
        Dim csvFile As Workbook
        Set csvFile = Workbooks.Open(filePath & fileName)
        
        'Load the data stored in the first cell into an array
        'Assumes the data is separated by ;
        'If not, change the delimiter to whatever type your files use
        Dim data As Variant
        data = Split(csvFile.Sheets(1).Range("A1").Value, ";")
    
        csvFile.Close SaveChanges:=False
        
        'Define the range in the target file where the stored data should be copied into.
        'Range("A1048576") is the last possible row of an Excel sheet
        'End(xlUp) jumps up from the last possible row to the last one filled with data
        'Offset(1) moves one row below, to the first empty row in the file
        'Resize(1, Ubound(data) + 1) expands the range to the number of columns determined by the copied data in the array
        targetFile.Sheets(1).Range("A1048576").End(xlUp).Offset(1).Resize(1, UBound(data) + 1).Value = data
        
        'Fetch the next file in the folder
        fileName = Dir
        
    Loop
    
    targetFile.SaveAs fileName:="C:\Example\Output.xlsx", FileFormat:=xlOpenXMLWorkbook
    targetFile.Close
    
    Application.ScreenUpdating = True

End Sub

 

Also, on a more general note, when you write these routines, get into the habit of declaring your variables where you need them. You tend to have declaration blocks right at the top for everything, which, coupled with the terse names of your variables, makes it hard to understand what types they are and why you've used them. Especially since you rely too much on the object datatype, instead of using more appropriate ones. Like when you want to refer to a workbook, define the varible as a workbook. That way you also get the autocomplete feature for the methods of that type. 

I do have one more favor, that I'm hoping to learn from you. How would I have the loop ignore string values that don't contain the specified delimiter? In this case, my chosen delimiter in semi-colon.

[Avocado Diaboli]

6 hours ago, Bryan1992345 said:

I honestly don't even know what declaring is or even how to properly format vba.

Declaring variables is basically telling interpreter how much memory to allocate to any given variable. You're not strictly required to declare variables in VBA and just leave it guessing what datatype to use (generally the one with the biggest memory footprint), but it's strongly recommended. That's what the Dim statement is for. Unfortunately, even Microsoft recommend grouping all the Dim declarations at the start of any given module, which is why you see it a lot in code snippets from people online. That's fine for short functions where not a lot is happening. But anything of substance is better served by declaring a variable at the spot in the code where it's first used.

 

1 hour ago, Bryan1992345 said:

I do have one more favor, that I'm hoping to learn from you. How would I have the loop ignore string values that don't contain the specified delimiter? In this case, my chosen delimiter in semi-colon.

In that case you can check the length of the array with the Ubound() function, which stands for upper bound. If the array has an upper bound of 0, meaning the entire string is represented at the 0 index of the array, then the original string can't have contained the delimiter. If it's anything else, it must have contained the delimiter at least once. 

 

Dim data As Variant
data = Split(Range("A1"), ";")

If UBound(data) = 0 Then
	'String contains no delimiter
Else
	'String contains delimiter
End If

 

[Bryan1992345]

21 hours ago, Avocado Diaboli said:

Declaring variables is basically telling interpreter how much memory to allocate to any given variable. You're not strictly required to declare variables in VBA and just leave it guessing what datatype to use (generally the one with the biggest memory footprint), but it's strongly recommended. That's what the Dim statement is for. Unfortunately, even Microsoft recommend grouping all the Dim declarations at the start of any given module, which is why you see it a lot in code snippets from people online. That's fine for short functions where not a lot is happening. But anything of substance is better served by declaring a variable at the spot in the code where it's first used.

 

In that case you can check the length of the array with the Ubound() function, which stands for upper bound. If the array has an upper bound of 0, meaning the entire string is represented at the 0 index of the array, then the original string can't have contained the delimiter. If it's anything else, it must have contained the delimiter at least once. 

 

Dim data As Variant
data = Split(Range("A1"), ";")

If UBound(data) = 0 Then
	'String contains no delimiter
Else
	'String contains delimiter
End If

 

Thanks again for the help. This was big. So, translating to Python, Dim is basically the same as making a variable global within a defined routine. Because all scripting language is effectively the same with exception to the syntax, would you say the same advice would be applicable within Python? Usually, I don't make a variable global unless it is going to be used again within another routine. I had suspicions about what Dim serves, but as you said, a lot of projects usually have all variables declared.

[Avocado Diaboli]

2 hours ago, Bryan1992345 said:

So, translating to Python, Dim is basically the same as making a variable global within a defined routine. Because all scripting language is effectively the same with exception to the syntax, would you say the same advice would be applicable within Python? Usually, I don't make a variable global unless it is going to be used again within another routine. I had suspicions about what Dim serves, but as you said, a lot of projects usually have all variables declared.

Not really, Dim serves the same purpose as Var does in JavaScript. It's just a keyword to let the interpreter know that you're declaring a variable, because you can't declare and initialize a variable in VBA at the same time. A lot of language let you do something like this:

int i = 0;

This declares a variable of the type integer with the name i and initializes it with the value 0 in something like C or Java.

 

I can't tell you why VBA requires the use of Dim when declaring a variable. My guess is that since declaring a data type in VBA is optional, you have to have a keyword that tells the interpreter that you intend to declare a variable. These are all valid:

'no declaration, direct assignment of a value to a variable of the default Variant data type
i = 0

'declaration without type, which defaults to the Variant data type, with assisgnment later on
dim i
i = 0

'declaration with a type, with assignment later on
dim i as Integer
i = 0

Now I'd argue that if you're not gonna bother typing your variables, the second example is pointless. But since the language still allows you to declare a variable without an explicit type, you'd could end up with a line that basically just states the name of the variable with nothing around it, if you weren't at least required to use the Dim keyword. But that's only speculation on my part.

 

 

To make a variable global in VBA, you need to declare it outside of a subroutine with the Global keyword, not the Dim keyword. Like this:

Global myVal as Integer

Sub First_Sub()
	myVal = 1
    	'etc.
End Sub

Sub First_Sub()
	myVal = 2
    	'etc.
End Sub

This allows you to use myVal anywhere in the code. Obviously, that's generally not encouraged, since variable scope is typically recommended to be kept as narrow as possible, but there might be certain instance where you just can't get around it.