Modules & VBA :: Possible To Transport Variable From Access To Excel?
Aug 7, 2015
I have some shapes to be automated in powerpoint (Tables, Pictures and Charts). Each and every shape has to be created on basis of some reports (all excel files with a Dyno-Static Filenames: "Some Bond TE 1-29-2015.xlsx", "Scenario Analysis GSB 28th Feb 2015.xlsx"). Dyno-Static Filename: Filename having some part static and some part variable in it.
So I created a table with a list of file names (report names) with wild cards on it. Like "Some Bond TE*.xlsx", "Scenario Analysis GSB*.xlsx". I am opening these files using the following code from Access:
Code:
If Not IsWorkbookOpened(Dir(CurrentProject.Path & "Received FilesFiles" & SlideNshapes!Shape_File)) Then Set SourceFile = Excel.Workbooks.Open
(CurrentProject.Path & "Received FilesFiles" & Dir(CurrentProject.Path & "Received FilesFiles" & SlideNshapes!Shape_File), ReadOnly:=True)
There is no problem in Access Part. Now I come to my current problem. I am planning to have an attachment field (excel file) to store a table layout model identical to the powerpoint table (shape) to be automated.
Suppose:
The powerpoint table is like the following:
Rates Wtd Dur
US2Y 0.34
The Attachment Layout:
Rates Wtd Dur
US2Y =FilepathfromAccess!Sheet1!M24
There are so many cells to lookup on different excel files. The Following are the questions:
1. how to passs an access variable to a formula in a excel sheet?
2. How to refer only the opened sheets in an excel formula?
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
I am trying to import data from an Excel file to MS Access (2013).
The Excel sheet consists of 700 columns. A group of 7 columns (always same header) are to be implement in Access one among the other.
That means:
At first columns A-G, than columns H-N, than columns O-U
I am trying to solve this with a loop (as you can see in the code)
But, if I start the modul I get this error:
"Runtime error 1004. The method Worksheets for _Global object failed"
But the module works, of I start it a second time. The error message appears only at the first start.
Apart from that, the code works. Sometimes, the module imported empty rows into the Access table.
Code: Sub ExcelImport() Dim xlApp As Excel.Application Set xlApp = New Excel.Application xlApp.Workbooks.Open FileName:="C:Users...DesktopBeispiel.xlsx"
Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.
How can I export the list of graduates to multiple excel files, with the filename based on "year"?
I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"
But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.
Excuse me! i would like to know how to use browse function (like in windowXP) to select the excel file i want instead of using transferspreadsheet code.
I am trying to create a process which selects a customer's site identifier and uses that to query an Oracle database through an ADODB connection. Which is all well and good until that customer has more than one site.
Here is a simplified version of what I have created:
Code:
'Get site details from current database on basis of company selected on form
Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection Dim RsSites As New ADODB.Recordset RsSites.ActiveConnection = cnn
[code]....
As I said, this works perfectly when the customer only has one site, but I have failed to find a way to turn a recordset with multiple values into a SQL variable that can be used in the IN clause.
I have attempted to convert the recordset to a string using GetString, but could not find a way to correctly seperate out the records.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code: Option Compare Database Option Explicit Public Sub Populate(lngParameter As Long)Dim frm As Form
Hi People, great forum and loads of info to keep me going.
just a quick question
i have created a vehicle managment db, with a list of all our vehicles and there information such as tax due date, MOT Due date and copys of log books and MOT Certs,
what im wondering is, can i create an alert so it will alert me either by email or by a pop up box or maybe a printed document telling me a few weeks before the MOT or tax is due, i'm only really basic with ACCESS and im using the new 2007 version, the forms etc work fine and i have no problems with trying things out
maybe one nice person could point me in the right direction
I am trying to set up something to be able to take me to a folder that holds various forms for personnel. I have a query that generates the link for each person. I have tried to set it up as a hyperlink in ACCESS 2013 and it displays as one but doesn't act like one.
I want to be able to click the link and have it open up a personnel folder for that individual. I can't find a MACRO that I can create to do it. Example of my query is that it creates a link K:Main BreakdownSection BreakdownPersonnel FoldersName and the name is the variable part.
My next problem will be to have it create that folder when we have new personnel arriving.
I would also note that I have not worked with ACCESS in many years and much of what I was able to do with 2002 and 2003 doesn't work with the newer versions of ACCESS.
I'm trying to import an excel file that includes a column of Part Numbers that I am interested in. I am trying to import that excel file into an access table called 'TableForImport'. That table has a column called 'PartNumber'. Thing is it's not just one Excel file that I am uploading...although I am uploading one file at a time. So the column name for each excel file table is different but they all have one column for the Part Numbers that I am interested in.
My code won't allow me to import the excel file into table 'TableForImport' unless they have the same column name! They never will! How do I accomplish my task without editing the excel file information? Here is my code:
using VBA in Access and Excel. I have an Excel form that I am using to collect data as the front end and an Access database to house the data as the back end. In my Access database I want to create a module that will open the excel files and retrieve data from Active X combo boxes, option buttons and checkboxes that are on the Excel form. I have been searching for the VBA code to get data from these objects but have not been able to find any examples. How to reference these objects and get the values from the excel form
I have two problems excel instance does not close at the end and two when I try to do the looking using text it works but soon as I try using date/time it returns nothing.
Code: Private Sub Command84_Click() Dim objExcel As Excel.Application On Error Resume Next
I am wondering if there is a way to disable the "saveas" in excel from access with vba? I have the following code:
Private Sub cmdReport_Click() Dim path As String Dim XL As Object
[Code]....
Which I have used in another excel file. But I can't use that now. The file I am opening from access is a workbook generated from an excel template, and I have yet to find a way on how to transfer this Workbook_BeforeSave sub from the template to the new workbook.
I'm exporting some data from an Access Database into an Excel Spreadsheet to create a report in Excel, and I'm running into some problems with that.
The idea is I have an Excel Template with a tab to take raw data from an Access Query, and then a couple tabs that manipulate that data and a final tab to format it for reporting. Basically, the formatted tab uses lookups to find data that's been exported from the query and display them in appropriate columns and rows (producing a result sort of like a cross-tab query but where I've got better control over the presentation of the results). My end-goal is to have this data formatted, to replace all the lookups with hard-coded values data, and then to remove the raw data tab and other tabs with lookup data to have a clean report ready for printing.
It's that last step - deleting all the extraneous tabs that are no longer feeding any equations because I've copy/pasted values over the lookup equations - that's proving vexing. Everything through this point is in the same On_Click event code:
Code:
Private Sub Command0_Click() 'Note: I'm using early binding right now to take advantage of Intellisense; 'My plan is to go back and replace this with late binding once I have code that works. Dim objXLApp as Excel.Application Dim objXLbook as Excel.Workbook Dim objXLWS as Excel.Worksheet Dim strPath as String
[code]....
I don't get any error messages. I actually have a messagebox thrown up in the code to inform me that I'm getting to the right worksheets selected. (The loop is selecting the worksheets as expected. But when I open the file in Excel... all of the worksheets are all still there, including the ones that should've been deleted.Is there something I should be doing to make sure the worksheet deletion goes through?
I have an access database that is used to store records of requests for for items. Example, ID, WhoRequested, CustomerName, Date, address, phone, WhatRequested, amt. I take this data and export it to an excel spreadsheet and send to another area for processing.
I then get the sheet back with the orderdate, ordernumber, and shippingnumber. I need to update the original table with this new information. it is not bad when there is only a few, but Ihave had 100 or so lately, and updating each record manually is time consuming.
I'm stuck on a step where I want to import an excel worksheet into the msaccess like we do normally. I do not have any data inside, it's just the header I will be importing. The data will be feeded by other forms based on some selections. My requirement is the "Default value" of each field should be set to 1 as we see in the property of a table in design mode.
The data would be updated later for some fields via macro or commands, but the fields were nor touched should be set to 1 (Value).
I've got a table with data about a contract. Each contract has his own ID. For each contract i have Information from SAP, Information from a System called geris and a System called pauschale. No I would like to Export that to Excel. With VBA, I would like to transfer the data for each ID to each spreadsheet.
I have Query call "export to excel" these are columns in my query
employee id total ex date of ex first name surname
which I would like to export to excel file name "access data"
columns in excel A employee id b total ex c date of ex d first name e surname
now my problem is I cant manage to export the data to existing sheet within excel when I export it opens the existing file but create a new sheet / tab but I just want to delete the data in columns A,B,C,D only refresh the data in these columns when the user hits the command button in access on my form and takes the data from my query
Function SyncSuppliers() On Error GoTo errhandle Filename = DLookup("SupplierPath", "Setup", "SetupActive = True") If Filename = "" Then Exit Function End If Set xlapp = CreateObject("Excel.Application")
[code]....
The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row A-BELCO LTD will import (HADAR LIGHTING) does not.
I have a table (tbloutput) which has details of customers and which staff they have been contacted by.
What i want to do is, export the details from this table into an excel sheet using a template that i have set.
What i want to do is create multiple excel outputs using this template depending on the name of the staff. So each staff will have a seperate workbook which was created using that template. And i also want the new workbook to be named for that staff member.
So in short
Table exported to excel workbook and excel workbook named : Blabla staffname.xlsm
