2

Is there a faster way to import a column from a specific excel file as an array with VBA?

The code that I'm currently using has to open the excel file. Is there a way to do this in the background? Is there a way to read values row by row from the first column?

Thanks

My code below:

Sub LoadExcelArray()

Dim Vendor As Variant
Dim wb As Workbook
Dim sFile As String
sFile = "D:\Desktop\test.xlsx"

Application.ScreenUpdating = False

Set wb = Application.Workbooks.Open(sFile)

Vendor = wb.Sheets(1).Range("A1:A95").Value2

wb.Close False

Application.ScreenUpdating = True

MsgBox Vendor(30, 1)

End Sub
Sam
  • 155
  • 1
  • 14

1 Answers1

0

What you're already using is the best way in my opinion. But if you're looking for other options: The xlsx file is actually a zip file. You could open it as a zip file and extract the contents. This can be done without starting Excel. So it should be faster.

https://msdn.microsoft.com/en-us/library/dd922181(v=office.12).aspx

You can save the spreadsheet as a text file or you can save it as a CSV file. The data will look slightly different between those two options so use whatever works for you.

If your data will never have tabs in it, then tab delimited(.txt) is the way to go. Open the file, split by rows and then split by columns. Done.

D_Bester
  • 5,723
  • 5
  • 35
  • 77
  • Here they talk about writing to xlsx https://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp – D_Bester Aug 08 '18 at 17:54
  • Thanks for the input. I think I'll put a BeforeClose function on the workbook, saving it as a .txt and then importing the .txt file into vba with `Split` (where each new line is a different element of the array). Shouldn't it be faster? – Sam Aug 08 '18 at 17:57
  • Sure, it's much faster to read/write text files than Excel files. Might consider CSV too. – D_Bester Aug 08 '18 at 17:59
  • Would `CSV`s be simply faster than `xlsx`s with the code above or can they be also be `Open For Input` as `.txt`s are? – Sam Aug 08 '18 at 18:15
  • CSV is only faster if using `Open for Input`; no speed increase if you open it as a spreadsheet. I only suggested it as another option if you prefer comma over tab. – D_Bester Aug 08 '18 at 18:27
  • There's a problem with CSVs. When I import the the column of the csv, the first element of my array has this symbol preceding it: ``. Solutions online have suggested to set `fileEncoding="UTF-8-BOM"`. Any idea how? – Sam Aug 08 '18 at 19:41