I am in the middle of a long winded process of automating a daily letter print job. I do not have access to VB so I am trying to do everything in VBA;
The process in brief;
x amount of files are produced and placed in a particular folder, each file that is letter releted needs ro be opened in excel, indexed, sorted, formatted and then saved an closed. Each relative document in MS Word is opened, merged with the file and then printed to specific printers. The filenames of the datafiles change everyday.
What I want to do;
Obviously I want to automate the whole process but at the same time I am designing it aimed at people that are not very computer literate so it needs to visually easy to understand and process. I want to also copy all the data from the datafiles into worksheets, so I will be merging the MS Word documents with these instead, this cuts Word having to re-locate the files everyday.
What I have so far;
I have written some code that checks and displays all the available files in the folder, then it opens all the letter files I require into excel, visualy everything is all set up with userforms etc all ready to be coded. What I need to do no is copy all the data in these datafiles into the relevent worksheets, my problem is that I only need to copy rows and columns that have data in, this is to stop MS Word showing blank records.
Each specific file has a 3 code identifier at the beggining of the filename, I use this to identify which letter is which, the files that I want and that are available are stored in associated cells within my main worksheet, each letter type now has a variable name for its datafile in VBA, my idea is to have another variable counting the number of rows and columns in each datafile, then use this in my copy code to select only the data I want rather than the whole file.
This works fine if I use descriptive references rather than variables, e.g
--------------------------
-------
Dim dt407 As Variant
Dim letter As String
Dim both As String
Dim Test1 As Integer
dt407 = Sheets("info1").Range("E5"
)
both = Sheets("Dat1").Range("G25"
)
letter = "A1:" & Sheets("Dat1").Range("F25"
) & "1"
If Sheets("Info1").Range("K5"
) = "Not Present" Then
Else: Sheets("407").Range("A1").
Formula = "='" & dt407 & "'!A1"
Sheets("407").Select
Selection.AutoFill Destination:=Range(letter)
, Type:=xlFillDefault
Range(letter).Select
Selection.AutoFill Destination:=Range("A1:" & "Both"), Type:=xlFillDefault
==========================
==========
======
Without explaining the code too much (Theres alot of cell references I know !) my problem is that I cant count the number of rows and columns of a file that is open by using a static formula and/or code (By static I mean one that I do not have to change everyday and that it uses the variable to alter what file the code/formula should be looking at) Please can someone help me, either modifying the way I am trying to do it or equally a totally different way! Whichever!
Thankyou
