Topics Search

Excel Automation - Blank worksheet/file

Excel Automation - Blank worksheet/file
Views: 1
Dim appExcel As Excel.Application
Dim ExcelWbk As Excel.Workbook
Dim ExcelWks As Excel.Worksheet

Dim sTemplate As String ' used for Template file path
Dim sTempFile As String ' used for Template file neame
Dim sOutput As String ' used for Output file name

Dim lRecords As Long ' used for count of number of records
Dim iRow As Integer ' Spreadsheet row index
Dim iCol As Integer ' Spreadsheet column index
Dim iFld As Integer ' Record field number index

Const cTabOne As Byte = 1 ' used to select 'Sheet 1'
Const cTabTwo As Byte = 2 ' used to select 'Sheet2'
Const cStartRow As Byte = 7 'initial Row index
Const cStartColumn As Byte = 1 'initial Column index
Sponsored Links:

Similar posts...

 

Peforming Excel operations via Acces automation

Peforming Excel operations via Acces automation Icon
Access 2007 issue, however could relate to earlier versions.

I have an Excel template that has a series of blank rows

Row1 headings
row 2 headings
rows 3 to 30 blank rows
row 34 formulas to sum values in columns =Sum(B4:B33)

Repeated about 5 times further down the worksheet

Access opens the workbook and uses CopyToRecordset to populate Excel from row 4 downwards. Will never be more than 30 rows.

But what I want to do is when the copy has taken place I want to remove blank rows when a row in column B is blank.

What I did was something like

For X = 4 to 250
If Range("B" & X).Value = "" Then
delete the row
End If Next

But could not get it to work
 

Trying to run a macro in Excel from inside Access

Trying to run a macro in Excel from inside Access Icon
I've been opening comma delimited files in Access, used an Excel function to re-save those files in .xls which Access readily understands, then opening said files and running a query on them to organize them.

Now the file needs to go to Excel where it is entered into a worksheet with a certain heading.

Problem is when opening the .xls converted file in Excel, no macros show up. If I open a blank worksheet in Excel my macro shows up. I lowered the security settings to the most basic level in Excel.

How do I make the macro show up in the file opened in Excel through the Access VBA, so I can run it?
 

Importing the second excel worksheet

Importing the second excel worksheet Icon
I'm trying to import the second worksheet in an excel file to access. I am wondering if this is possible
 

objXLApp.Visible = True Issue

objXLApp.Visible = True   Issue Icon
I have a good size project in VBA sending information from 2 different queries to an Excel sheet which maps and saves the file. I want to open the new excel file so my code looks like this at the very end

objXLApp.Visible = True

I want to open the file with the second worksheet showing. So how can I have the file open, but show the second worksheet
 

Importing certain cells from a specific Excel Worksheet and File into a field

Importing certain cells from a specific Excel Worksheet and File into a field Icon
I'm working on a database project as an intern and was asked to create a simple database. I know a little bit of VBA coding (not a WHOLE lot) and I was wondering if their was some pre-existing code that automatically, when I click a button in a form, allows the user to select the worksheet and file that he/she would like to import data from (Excel file that is) andautomatically grab the data from a certain cell range and place them in their appropriate fields in Access.
 

Import Worksheet From Excel 2010

Import Worksheet From Excel 2010 Icon
You can share data between Access 2010 and Excel 2010 in many ways. Excel worksheet consists of cells that are organized into columns and rows, Access recognize them as fields and records. Access 2010 provides an easy way to import Excel worksheets, this post will explain in detail how to import worksheet in Access from Excel 2010.
 

Importing excel file to append a table

Importing excel file to append a table Icon
Is the record is already existing in access, but a few fields are blank, is there any way to import an excel file that just updates those blank fields for the specific record?

For example,

I have an excel file that includes files that are ready to ship out. My access database already has those file names, but does not have the shipping box number. Is there any way to importthe excel file into my database, have it find those specific file names and update the shipping box number field? The excel file won't be adding any new records,
 

Macro to paste data from Access Tables to Ms Excel

Macro to paste data from Access Tables to Ms Excel Icon
Please find attached sample MS Acces db and Ms excel file.

I need the data from the 3 Access tables to be populated across to the three tables as shown in the MS Excel file.

Assume the file path of the excel file is: C:\Documents and Settings\All Users\Documents\microsoft\Sample_File.xls

so the table Sample_Data_1 gets populated across to MS Excel worksheet Records_1 C11:F14 and so on.

In the original file I have a sub form which runs the macro.
 

Excel Automation Select Range Only with Data

Excel Automation Select Range Only with Data Icon
Have some Access code using Excel Automation that pastes data from a query into Excel. I want to then be able to select all data that is in column F.starting with F1.

The data in column F will vary, but will be contiguous. So, at any given run of the code, column F could be populated from F1:F5 or at another time F1:F10 etc.

Once selected, I want to shade the range.

Any Access code automation help for this is appreciated.!
 

Pulling Dates from 3 different columns

Pulling Dates from 3 different columns Icon
I have a query that pulls data & dates from other tables (Note, the tables are actually linked Excel Worksheets). On my query I have 3 date columns; "DUE", "REVISED", and "ESD". What I'd like to do is create a single column that will pull the correct dates accordingly: Always pull from the "ESD" column unless it is blank.

If it is blank then pull from the "REVISED" column. And, if the "REVISED" column is blank,

This is the formula I am currently using, however it is showing blank cells where it should be showing a date:

DUE DATE: IIf(Not (IsNull([ESD])),[ESD],IIf(Not (IsNull([REVISED])),[REVISED],[DUE]))

I'm thinking the blank cells are coming up because of incorrect formatting possibly on excel worksheet. I've tried changing the formatting to DATE on the excel worksheet cells, and as soon as I link it again with Access it shows the "REVISED" column as TEXT rather than DATE.
 
Sponsored Links: