How to connect Access-Excel data input - excel

I am trying to think of solutions to my problem.
I have:
A - A list of numbers in excel (lets say a uniqueID), this list changes every few hours
B - A table in Access that has every uniqueID with an additional column of information (lets say its a count , call it X).
My day-to-day workflow involves have an excel sheet full of uniqueIDs with a blank X column. Currently I am importing the excel sheet into access, running an update query then copying the information back into excel to send on to a colleague. I do this every few hours and its quite painful, I know there is a coding way I can do this easier and have been experimenting with the idea of a VBA macro that pulls data directly from the access table and fills in my column in excel.
Is this doable? Im not really sure how to go about it or even what to search in google for more help.

You can create a new instance of Excel object, read required data, then dump X values back to xlsx using CopyFromRecordset function. Here is typical code:
Dim rs_data As DAO.Recordset
Dim xls_App As Object 'Excel.Application
Dim xls_WB As Object 'Excel.WorkBook
Dim xls_WS As Object 'Excel.Worksheet
Set xls_App = CreateObject("Excel.Application")
Set xls_WB = xls_App.Workbooks.Open("C:\temp\MyDataFile.xlsx")
Set xls_WS = xls_WB.Sheets("MyWorksheetWithData")
Set rs_data = CurrentDb.OpenRecordset(str_SourceQuery)
xls_WS.Range("A1").CopyFromRecordset rs_data
rs_data.Close
xls_WB.Save
xls_App.Quit
Also you can read the data from xlsx directly using SQL. Here is an example how to transfer data from Excel to existing Access table using SQL:
INSERT INTO Table1 (uniqueID, SomeOtherColumn)
SELECT *
FROM [MyWorksheetWithData$A1:B100]
IN "C:\Temp\MyDataFile.xls" [Excel 12.0;HDR=YES;IMEX=0];

Related

iISSUE SOLVED] - THANKS New Issue, defining Fields/RecordsWAS Why can't I use DAO in VBA Program written in Excel 2013

Please refer to latest comment by me as I have one more follow up question about column names/reassigning header row. Thanks! KDL!
OK, I am able to Open the "database" so I can set up queries of recordsets using DAO, and I have figured out how to define a named range so I have a variable that can act as the "Field Name". I am having an issue with VBA code to insert that Named range into the SELECT SQL statement. This is the relevant section of code that I am still having a problem with
BEGIN CODE
'**********************************
Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim refRngItemSKU As Excel.Range
Dim LastRowAsIngeger as Integer
Dim INVENTORYTABLE as String
Dim strSelectRS as String
' ************************
LastRowAsInteger = 1764 'Value set in code for now, will be formula later to make dynamic
Set refRngItemSKU = ThisWorkbook.Worksheets("Vendor Inventory").Range("A11:A" & LastRowAsInteger)
' ******* NOTE when I insert refRngItemSKU.select the correct range of cells are selected **********
INVENTORYTABLE = "D:\My Documents\Leavers Switches And More\INVENTORY\COLLECTIONS AND DISTRIBUTOR INVENTORY\Model Train Inventory - Updated 6-7-2017.xlsm"
Set db = DBEngine.OpenDatabase(INVENTORYTABLE, False, False, "Excel 12.0;")
strSelectRS = "SELECT * FROM [Vendor Inventory$] WHERE [refrngitemsku$] IS NOT NULL"
Set rst = db.OpenRecordset(strSelectRS)
'**********************************************
END OF CODE
Now everything up to the last line of code works. When it reaches the "Set rst" line, Error 3061 comes up. The error states "Too Few Parameters. Expected 1". When I am in Debug mode, I notice the "refrngitemsku" variable is not returning a value in the "strSelectRS" string where the actual value should be showing up. So any help in correcting the Syntax so I can return a value, ie. the actual range of cells that the refrngItemSKU variable is supposed to return. Thank you again. Sorry for having to edit my Question, but the comment section does not allow for enough letters to type all this out. To the moderators, I appreciate your patience with my posts. But I am desperate for answers, and so far, this forum is the only one that have been willing to answer my questions! Thanks again.
Ken L.
POSTED EARLIER
GOOD NEWS! Thanks to the suggestion of using ACEDAO.DLL that seems to have resolved the first problem, but a new one has crept up. I can't find a way to use DAO, in Excel 2013-64, using SQL to query the various tables. It seems I can not define "Fields" and "Records" whereby the Columns are the "Fields" and the Rows are the "Records". Though when I do a recordcount on the record set, it shows the total number of rows that have data on them. But when I try to identify a column (I.e. Field) to set conditions on which records to select, I get syntax errors. Any help would be much appreciated, and thanks again to all of the posts, they all have been helpful as it caused me to google specific terms to find solutions to my current project! THANK YOU THANK YOU THANK YOU!
Ken L
ORIGINAL POST:
I have searched using google to find a specific answer with no luck. I keep getting error code 429 when trying to use DAO language "OpenDatabase" so I can use SQL Language to search various worksheets in one Excel 2013 Workbook. I am running Windows 7-64 and Office 2013 64-bit. From what I have read, it would appear DAO is not supported anymore by Microsoft, and even ADO doesn't look like it is looked favorably by the folks at Microsoft anymore. I really don't care if I can't use DAO anymore as long as if there is a method where I can use similar functionality to find specific records based on specific criteria, amend certain records, create other records, and move amongst records (i.e. .movefirst, .movelast, etc.). If there is a different VBA language that is usable for 64 bit versions of my software, I will learn it.
I rather not use the workbook, worksheet functions and using IF-THEN statements going through the entire table trying to find certain "records" that match the criteria I am looking for. It works, but its tedious and I much rather use SQL type language.
I realize using MS Access would be more appropriate, but I prefer using the Spreadsheet format. What I am trying to do is write a VBA program that converts certain cell values into a certain format to be converted to a csv file to upload to a website for my web-store. The file is my inventory sheet, with tons of calculations/formulas in it. I rather not use MS Access if I don't have to.
Thank you for any help that can be provided!
Thanks.
Ken L.
You can use ADO:
Dim conn As New ADODB.Connection
conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties=""Excel 8.0;HDR=YES"";")
Include a reference to Microsoft Office 15.0 Access database engine Object Library (i.e use AceDAO instead of Jet DAO; drop any reference to DAO 3.6)
OpenDatabase([filepath], [Exclusive], [Read only], [Connect string])
OpenDatabase([filepath], False, True, "Excel 12.0 Xml;HDR=yes;")
Excel 12.0 Xml = .xlsx
https://msdn.microsoft.com/en-us/library/office/ff965871(v=office.14).aspx#DataProgrammingWithAccess2010_DirectDAOExample
ADDED:
OpenRecordset("SQL statement", ...)
In SQL statement:
Tables can be SheetName$ or SheetName$-plus-range-specifier or RangeName
Add a $ onto the end of sheet names (e.g. Sheet1$)
Tables (Excel lists) probably aren't recognized; you may need to explicitly define an overlapping range name using cell notation (e.g. a1:b10)
SQL example (you can enclose Table and Field names in brackets [] to escape characters that may conflict, e.g. spaces):
SELECT * FROM [Sheet1$] WHERE ([Field Name in Brackets] = SomeNumericValue)
SELECT * FROM [Sheet1$] WHERE ([Field Name in Brackets] = 'SomeTextValueInSingleQuote')
SELECT * FROM [Sheet1$] WHERE ([Field Name in Brackets] = #SomeDateInHashTags#)
Syntax help:
https://msdn.microsoft.com/EN-US/library/office/dn123881.aspx
ADDED 2:
you can't define a range as a variable in your VBA and then pass along that variable name in the SQL statement; the range has to be defined as a named range in the Excel workbook itself (e.g. using the Name Manager dialog in Excel or VBA code), then on the database engine side it can look up that name from the SQL statement text against available table names;
you can use VBA to create a defined/stored named range using something like:
ActiveWorkBook.Names.Add(Name:= "MyRangeForSQL", RefersTo:= "=A11:E1764")
if no field names in the first row, then you need to change the connect string to indicate no header row by including "HDR=No;" ; Ace/Jet will then create some default field names: "F1" - first column, "F2" - second column, "F3" -etc. e.g.
strSelectRS = "SELECT * FROM [MyRangeForSQL] WHERE [F1] IS NOT NULL"
ADDED 3:
you can always alias the (table and) field names in the SQL statement: [FieldName] AS [FieldNameAlias]
strSelectRS = "SELECT [F1] As [Sku], [F2] As [Quantity], [F3] As [Price] FROM [MyRangeForSQL] WHERE [Sku] IS NOT NULL"
should probably mark this question as answered, getting outside of original scope; good luck

Inserting SQL Server Results into Excel

I have an Excel spreadsheet like this:
Where the Student Name, Student ID and Classification are, I would like to fill that with the results from a SQL Server view. The columns to the right (Capstone, Milestone 2, Milestone 1, Benchmark, Semester Grade, Notes) will remain blank until the instructor fills them in later. These columns will not be written back to the database but will be saved, with the data that is loaded from the database, into the first three columns in it's Excel spreadsheet format.
Question 1: Is there a way to simply "embed" the data that is coming from the view?
Question 2: If not, can you provide a link to an example using a macro to read records and insert them (moving lower rows down with each new record)?
TIA
You will need to use VBA to create an ADO connection, recordset, command, and parameter.
Dim adostudent as ADODB. (...) ^ use the above
Then, assign a named range to the areas that you would like to drop the information
StudentRow
Then, use a Do Until and an iterator and do until.eof and .movenext to drop the values from the recordset into the range.
irow = 10 'insert the header row # + 1here
Do Until adostudent.EOF = True
with adostudent
StudentRow(irow,1).value =.Fields("Student Name").value
...
...
.movenext
irow = irow + 1
loop
On the Data tab in Excel you can select "From Other Sources" icon from the "Get External Data" group to pull your student data from a SQL Server view.
That will give you the "embedded" data, until you decide to Refresh your connection and retrieve updated data from the view.
EDIT:
Use the CopyFromRecordset method for Range objects. Here is the link that provides working examples for what you are trying to accopmlish, without the range being being pushed to the side. Entries #3 and #11 provide the VBA examples.
http://www.xtremevbtalk.com/showthread.php?t=217783
You can use the Data->Get External Data->From Other sources->From SQL feature in Excel.
Or use my Add-In:
http://blog.tkacprow.pl/excel-sql-add-in-free/

Transfering Access Data into an Excel template sheet behind a form event

I am working on a new database in access to automate a lot of hand entry into Excel. Right now I have come to a point where Excel can graph and be distribeted easier than Access data.
I would like to place a button on a form that when pressed it will take data from Access and fill out an existing 'template' Excell sheet that has all the formulas and graph pulling from a set of cells. The data in Access (can be made into an sql query/record set in VBA) is just some totals, averages, and a YTD calculation and needs to be placed into a specific set of cells in an existing excel sheet and saved as a different one (as to not overwrite the template). It is only 13 x 5 cells of new data.
Is this possible?
Yes, it can be done. The Export will need to be meticulous though, because you already have a predefined range of cells you're using in Excel.
First step would be to get the data into the format you need - your SQL statement.
Make sure this format will work for your template and is returning the correct data.
Once this is done, you can write to your Excel file. This is the format.
expression.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding)
Here is an example:
Dim outputFileName As String
outputFileName = "C:\WhereYouWantYourFileToGo "
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "YourQueryName", outputFileName, True
MsgBox "Data exported to WhereverYouExportedItTo.xlsx"
Note: When using DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel12 is used for Excel 2010, the list of versions is here.
I'm assuming the rest will be handled by Excel.
It worked for me. You create the template the first time and save a spreadsheet with the same name as the given export. Remembering that it starts exporting to the first cell, if you want to insert the data later, just hide the desired cells above and save the file, when you open the workbook, it unlocks them. Thanks to Mark C. for the initiative.

Update access table rst with named cell values from excel

I have got a table in access that I loop through using a DAO recordset. For every recordset I take a bunch of data to an excel spreadsheet and run it through a model in excel. This produces a bunch of results in excel which are calculated in named cells.
I want to be able to update the current recordset in access with these results but am having a tough time doing it.
I have the following code
code to create a DAO recordset
code to move to first record
code to parse data to excel
code to run a bunch of stuff in excel including a goal seek to calculate results
next I use the follwoing code without success
With MyXL
strSQL = "UPDATE ProductPricing SET Profit = " & .Names("Profit") & ";"
End With
Code to move to next record and loop until EOF
Could you not try to keep everything in access and replicate the goal seeking using a user defined function? Failing that I would keep the code in excel and get the code to “pull” the values into excel and then “push” them to access as opposed to pushing the data to excel and pulling it back

SSIS 2008, Excel 2007 formatting excel columns properly (Exporting, not importing)

I'm running into some formatting issues with exporting data to an excel 2007 spreadsheet.
I have a template file that has the columns formatted correctly, but when the data is exported it doesn't get the correct formats (currency, short date, etc.)
I'm thinking I have 2 options
Have excel autorun a macro. (hate that idea)
Format the data before it gets to excel and make it string data only. (I don't like that idea either as you are going to get those irritating data conversion arrows next to each cell)
I've used the IMEX=1 trick to get around the data import issues before, but is there a special trick for exporting?
thanks,
I spent ages trying to figurure this out.
What you need to do is have a hidden row of data in the spreadsheet you are exporting to.
SSIS will place the values into the spreadsheet, and assume the type is that of the row immediately above. So the data row needs to be the last row in the spreadsheet, and the data in each column needs to be of the type you want it to be.
You would think there was a better way of doing this. There isn't. Sorry :-)
So after much formatting and gnashing of teeth, I came down to the final solution. It appears that you still need the hidden row at the top for excel to use. That determines whether the data is formatted as numeric or string. If you want to do any more formatting, then you need to add in a script task to your SSIS package. I just threw the following lines into a .net script task at the end of my processing and it fixed it right up.
Please don't puke on the DailyWTF quality code ;-)
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open("c:\reports\Report.xlsx")
'This is the long ass Accounting Format'
wb.Worksheets("Sheet1").Columns("E:E").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(#_)"
wb.Save()
wb.Close()
excel.Quit()
The solution is so simple it will make you scream. Just use the CREATE TABLE SQL command from a SQL task box in SSIS. Set the connection type to EXCEL. Here are the steps :
Let's use 'TEST' as the tab name on the spreadhseet.
1.) Copy the template file to a new file.
2.) Create a SQL task, set connection type to Excel and use the connection string to your newly created Excel file. Use the below code as a sample :
DROP TABLE TEST
GO
3.) Create another SQL task (using the same settings) and use the following code :
CREATE TABLE TEST
( Column1 Integer,
Column2 DATETIME,
Column3 NVARCHAR(50),
Column4 MONEY
)
GO
You can now start writing data. A couple of things I ran into is the I had to use the ` character instead of the ' for a comma. Also, I tried using both the DROP & CREATE commands in one SQL task but it didn't work for me, so I used two.

Resources