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?
View Replies
ADVERTISEMENT
Dec 17, 2014
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.
View 4 Replies
View Related
Jun 12, 2014
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"
[Code] ......
View 1 Replies
View Related
Jan 30, 2015
My access is 2013.
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.
View 14 Replies
View Related
Mar 15, 2005
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.
thx!
View 1 Replies
View Related
Jan 16, 2014
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.
View 5 Replies
View Related
Jul 8, 2013
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?
View 14 Replies
View Related
Oct 3, 2014
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
View 3 Replies
View Related
Jul 16, 2013
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
[Code] ......
View 5 Replies
View Related
May 24, 2007
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
thanks
Stu
View 2 Replies
View Related
Aug 20, 2014
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
View 7 Replies
View Related
Aug 5, 2014
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.
View 3 Replies
View Related
Jan 1, 2015
I have tried search through some forums and but still not able to get desire results.
Table Customer Agreed Transport Rate
Customer > Eff Date > Route > Rate
A > 1 June 2014 > AAA > MYR 99
A > 15 Aug 2014 > AAA > MYR 88
A > 1 June 2014 > BBB > MYR 77
A > 1 Sept 2014 > BBB > MYR 66
B > 5 June 2014 > CCC > MYR 88
B > 20 Oct 2014 > DDD > MYR 100
C > 1 Oct 2014 > EEE > MYR 222
If there is order from Customer A for Route AAA loading on 2 July 2014, I can easy to get answer "MYR 99".
How if I wish to make Route as primary key? (as I understand, Primary key must be unique and no repeat).
View 10 Replies
View Related
Jul 18, 2013
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:
Code:
CurrentDb.Execute "CREATE TABLE TableForImport " _
& "(PartNumber CHAR);"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TableForImport", Me.ExcelInput, True
View 8 Replies
View Related
Aug 18, 2014
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
View 7 Replies
View Related
Oct 4, 2013
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
[Code].....
View 11 Replies
View Related
May 14, 2014
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.
View 2 Replies
View Related
Aug 20, 2014
I need to freeze the panes in an Excel spreadsheet form within Access. I have the following code:
Code:
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
Set xlWSh = xlWBk.Worksheets(1)
With xlWSh.Range("B5")
ActiveWindow.FreezePanes = True
End With
The codes runs, but the panes are not frozen. (I got this from the Excel macro recorder)
I need both panes frozen. Where is my error?
View 5 Replies
View Related
Sep 30, 2013
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?
View 2 Replies
View Related
Jul 26, 2013
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.
View 1 Replies
View Related
Apr 15, 2015
VBA for a click button that extracts specific fields in an Access DB in to an Excel spread sheet.
View 9 Replies
View Related
Jun 6, 2014
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).
View 4 Replies
View Related
Sep 3, 2013
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.
View 6 Replies
View Related
Jun 22, 2014
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
View 14 Replies
View Related
Mar 25, 2015
I have a VBA function to syncsuppliers as below
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.
View 2 Replies
View Related
Sep 15, 2014
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
View 1 Replies
View Related
