Excel Partially Visiable

Feb 19, 2008

Hey guys, I have the below code that exports data to excel. Works wonderful, except when the Excel file opens, only the top portion of excel is visable. I would then have to save the excel file, then close both excel and access. Then once I reopen the excel file it is now visible. I would like the file to open over all other application including my Access application. Is there any way to force the excel file to open fully? Is there something I am missing. It seems to work the first time, but if I re-run the export the problem continues. Thanks

Private Sub cmdGetReport_Click()
On Error GoTo Err_Handler

MsgBox ExportRequest, vbInformation, "Finished"
DoCmd.Close acForm, "frmMonthEndRpt"
Application.FollowHyperlink "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"

exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub

Public Function ExportRequest() As String

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef ' Added 10/06/07
Dim sSql As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Dim strMacroName As String
Dim strFileName As String

strMacroName = "DeleteBlank"
strFileName = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"

Const cTabOne As Byte = 1
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
'sTemplate = CurrentProject.Path & Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOTemplate.xls"
sTemplate = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOTemplate.xls"
sOutput = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput


' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)

sSql = "select * from qryAOSummary"
Set dbs = CurrentDb

Set qd = dbs.QueryDefs("qryAOSummary")

qd.Parameters![txtStartDate] = [Forms]![frmMonthEndRpt]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmMonthEndRpt]![txtEndDate]

Set rst = qd.OpenRecordset
If Not rst.BOF Then rst.MoveFirst

' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow


Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AoOutput.xls"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

'Inserts Month,Year format based on entry in start date field.
wks.Range("A2").Value = [Forms]![frmMonthEndRpt]![txtStartDate]

'The Application.Run will run the Macro(s) that you saved in your spreadsheet
wks.Application.Run "'" & strFileName & "'!" & strMacroName

exit_Here:
' Cleanup all objects (resume next on errors)

On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

End Function

View Replies


ADVERTISEMENT

Queries :: Importing Excel File With Incorrectly Formatted Date Field - CVDate Partially Functional

Jul 22, 2015

I need to import an excel file with incorrectly formatted date field and it worked only to import them as text:

1 jan 2015
1 feb 2015
1 mar 2015
etc.

Using CVdate converts jan, feb, nov and dec to correct date, but gives an error message with mar to oct.

View 1 Replies View Related

Partially Fill Key Fields

Oct 29, 2006

I have a mainform Transactions with TransactionsID and a subform Site Details with SiteID. For non-database related issues (my users like things the way they are) at the moment I have to use text strings for the key fields instead of autonumbers.

SiteId begins with the TransactionID number (the program does check for uniqueness for TransactionID). At the moment the user copies and pastes the TransactionID into the SiteID textbox, and then just needs to add the site name.

I would like the SiteID to have the TransactionID partly filled in, so that one more step is eliminated. I have tried using forms!fTransactions!TransactionID, but I get #Error. I have also tried copying the TransactionsId text box onto the Site Details form and setting the property to invisible, and then using the forms! and I still get #Error. My first attempt was to change the data control of SiteID to TransactionID, but that didn't work either - it changed all the SiteIDs to their respective TransactionIDs. Fortunately I was working with a copy (rule number one of database design) and no harm done.

Is the issue that I am dealing with is that SiteID is a key field and that key fields can't be calculated? If so I will long for the day when all my key fields can be switched to autonumbers.

View 9 Replies View Related

Partially Deleting Text From A Cell

Jun 19, 2007

If I have the following value in a cell:

Joe <100,894> Doe

Is there a function in Access that will clear out the <100,894> leaving me with Joe Doe? To my understanding the Replace function only can replace certain characters. How can I delete everything in between the < > as well?

Thanks,
Paul

View 4 Replies View Related

Database Windows Partially Hidden, Can't Move It

Jan 11, 2007

This one has me stumped:When a particular database opens, its window is partially under (hidden by) the menu and tool bar. I can't click on the window's top in order to move it. I've tried several things (i.e. resizing the screen resolution, moving the menu and tool bars, resizing the window, etc.) with no resolution. Any suggestions.Am using Access 2003, Thanks, Joel

View 1 Replies View Related

Forms :: Create Partially Completed Datasheet On-the-fly For Data Entry

Dec 6, 2014

I have a primary school database. I'm trying to create a form that allows a teacher to select their class, then select a subject and then be presented with a data entry form in a table layout that lists only their student's names in one column and an empty column to input results for the selected subject.

To simplify my explanation to just three tables, lets say my tables are:
> Students....which stores student names plus a foreign key for their class
> Classes...which stores the class name
> Results...which stores all the results (fields are: ResultID (key), StudentID, SubjectID, Result, DateofResult)

I have no problems creating reports where the teacher selects their class from a combo box to generate a report based on a crosstab query. But this one has me stumped.

View 2 Replies View Related

Push Data From Current Record In Form Into A New Excel File, Using Excel Template

Sep 10, 2007

I searched the archive and didn't find quite what I was looking for, so..

I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.

I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.

Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!

A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.

I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.

Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject

View 1 Replies View Related

Linked Excel File - Excel Date Field Translated To Text?

Mar 23, 2006

I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.

My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.

I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.

Thanks in advance for your help.

View 2 Replies View Related

Access Query With Links To Excel / Export To XML And Back Into Excel

Apr 25, 2013

I am using Excel and Access 2010.

I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.

My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.

My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.

My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?

View 7 Replies View Related

Modules & VBA :: Export To Excel And Call Macro From Other Excel?

Aug 25, 2013

i want to export a table to excel , open this file and execute a macro from another file.

the code i have now is :

Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"

Opening the excel file goes ok, running the macro however not.

View 1 Replies View Related

Copy Data To Excel New Excel Workbook.

Sep 15, 2005

Hello,
sorry to post again my question but just cannot find a solution.
I have a table and would like to copy data into a template in excel. I know that the code will have to make a copy of the template and then copy the data into the new workbook into sheet1.

My table (table1) has 3 fields: SSN, FNAME and LNAME.
I want to copy these fields into cells B1 (for SSN), B2 (for FNAME) and B3 (for LNAME).
I will use a combo to select the recorset to copy.

My problem is how to copy data into the template. I understand that it is necessary to run a copy of the template and then copy the data into the new xls file.

Is there a way I can do this via code? Code help is appreciated. Thank you.

View 5 Replies View Related

General :: Access Data Export Into Excel As Data Linked To Excel

Oct 21, 2012

how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;

DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"

Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.

View 5 Replies View Related

Look Ups As In MS Excel

Jul 9, 2007

I am very good with computer programs, microsoft and the like.
However, recently i have been working on Ms Access. I am trying to make it do a look up as you would do in excel

whereby for example:

I could just type in a DVD ID on a form and it uses a look up to find the DVD name or vice versa but i havent been able to get it to do it.

Look ups on access just help in the creation of drop down boxes (combo box) or list box Which i dont want!!

Simply: i want ms access to "fill in the blanks" for me i.e the dvd name when i enter the dvd id

Help!! Please!!!

View 1 Replies View Related

Excel Vs Access?

Jul 17, 2005

We currently have a spreadsheet to track all of a clients medical's bills and keep a running total? I'm trying to decide whether to continue to track these medical bills in the spreadsheet or create a table and make it a part of the client db. I'm leaning toward keeping the spreadsheet. It seems to be a task a spreadsheet was designed for and I can link it to the db or import it as needed. Any opinions on which is better? What would be the reason to to give up the spreadsheet and make it a part of the db?

View 6 Replies View Related

Read From Another Excel

Oct 18, 2005

Hi,
Can someone help, does anyone know how I can read values from an excel file, basically I have an excel sheet that I'm doing some vba work and I need to go to the excel sheet which resides some where, read the table and apply the values in my current excel file. I hope that I didn't confuse you guys. Thanks a lot.

View 6 Replies View Related

Excel Link

Nov 3, 2005

Hi all,

I have added about 15 links on a form, linking to word and excel documents. The word documents open fine but all of my excel documents pop up with a messae saying the file cannot be open, yet when i just go to the document on my hard drive they all open fine...does anyone have any ideas please. Thanks

View 6 Replies View Related

Exporting To Excel

Nov 10, 2005

I want some guidance in regards to how should I export access form as a report to excel. I already have fields with formulas in it and here I have fields where I have written down the numbers, so when these numbers get exported to excel. Excel will be populated with with these numbers and all the calculation will be automatically done.

I hope I am making sense here.

I have fields in access that should export through a button and populate on excel sheet.

is there any vb scripting for this, I rem seeing something in the same context few months back, but I can't seem to find it rite now.

Thanks in adv

View 4 Replies View Related

Linking To Excel...?

Jan 15, 2006

I want to use the data contained in Access table in an Excell app. What is the best way to link them - preferably in 'real time' - is there a way excell can read directly from the tables? We need to analyse some of the data in the database through Excell....

Any suggestions welcomed...?

View 8 Replies View Related

Exporting To Excel

Mar 16, 2006

Wondered if someone could help.

I am trying to Export an access report from an .mde (File>Export) and receive an error message "Overflow". the report itself is only 17 pages long.

Can anyone advice?

Thanks

Paul

View 3 Replies View Related

Excel Vs Access

Mar 21, 2006

I'm new to Access.
I Work in excel but the Sheets start to have many records and, sometimes it's difficult to apply the formulas.
My doubt is: Itīs possible to make with the Access "everything"
that i make with Excel.
Example I apply formulas like:
=IF(ISNA(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";IF(ISBLANK(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE)))

Thanks in advance

View 2 Replies View Related

Link To Excel Doc

Apr 11, 2006

Hi,

I'm new to this so please go easy!

I have an excel document which automatically refreshes data gathered from an access db.
I need to put a button on the switchboard on the same db to open the excel document, basically to make it easy for others to find.
I have tried hyperlinks but it doesn't want to play. It locks the db and then won't refresh the information.
I know i'm missing something blindingly obvious, can someone help?!
:confused:
Thanks
elsiegee

View 1 Replies View Related

Excel Zip From Access

Jun 2, 2006

Hello,

Apologies if I have placed this in the wrong section but thought it was more access than excel.

I would like to on clicking a button, which simply has a close command behind it, for the database to zip the spreadsheet which it has just exported information to.

I have had a look round and tried to use the backup of database idea but could not convert it and did not know enough myself to change it.

I would greatly appreciate any help or guidance

Thanks

View 2 Replies View Related

Excel Canīt Calculate.

Aug 6, 2006

HI,

Excel is having difficulties calculating a workbook of mine. It complains about cirkular references, so I had to set the iterations manually.

The workbook holds about 5000 records and Iīm using a cosinus function.

The thing is I donīt know what to do. Excel calculates the workbook, but it doesnīt do it right. What setting should I use with the iterations?

Iīm thinking of running it in excel 97. Will that help?

I really need to be able to run this workbook. Does anyone have an idea?

Fuga.

View 6 Replies View Related

Access And Excel

Nov 22, 2006

Hi,

I was wondering if anyone might be able to help me here.

I have a question. Is it possible to create new worksheet in Access using VBA code?

What happen is that i have a form in Access that would export data from Access to Excel. I know that i could use the built-in feature provided in Access to export to excel. But because i guess i want to be more flexible in managing the data on excel spreadsheet. So would it be possible to write the excel programming in the Access?

Sorry for asking but i just want to know.

Thank you in advance

View 3 Replies View Related

Importing From Excel

Mar 6, 2007

Guys I don't know if any of you have done this. I need to import a formatted excel file into an access table, I know that I need to write a vba code to do it, can someone please give me a tip or an example. The excel file is a formatted form, it's not based on columns & rows!!!! Please Please assist.

View 1 Replies View Related

Excel To Access

Apr 9, 2007

Hello there
i have this field in excel called project desciption that is mor than 255 characters.

i wish to import the excel file to access

unfortunatley my access table only has 255 characters.

it is not letting me do it.

any suggestions.


Help

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved