Hi all

I have created a master spreadsheet which I have written a code for in order
to:

1. Delete all contents except the first row, which I have written as :

Sheets("Data").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("a2").Select


2. It will look at the file path and file name (as the original files are
saved in the same foler on a monthly basis), and then select and copy all the
data within the specified file path and paste it onto my master spreadsheet,
which looks like this:

qpath = [D14].Value: qfile = [D15].Value

'Opening workbook specified within the worksheet and copy all data
Workbooks.Open(qpath & qfile).Activate
Sheets("Sheet1").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Corporate File Preparation").Activate
Sheets("Data").Select
Range("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Windows(qfile).Activate
Range("A2").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Corporate File Preparation").Activate
Sheets("Control").Select
Range("B2").Select

Eventually I have inserted a few columns at the end of the data for date
stamp and other forumlas as well.

However, my problem arises when I copy this module into other master
spreadsheets, the formats and data are all different between them so what I
did was to change the file paths and some of the formulas. Everything works
perfectly, except when I link these master spreadsheets to Access, which is 8
in total, 7 of them are incorrect, due to the original module I written for
consists of 27 columns (included 3 columns i have added in using vba) and 853
rows of data. The other spreadsheets have less columns and less rows of data,
excel looks fine, but in Access, many blank rows and columns are also
imported up to 27 columns and 853 rows. I am not sure what I need to do here
as I will have to append these sets of data to another table in Access, it
also append those are blank too.

I think it's because my vba are written incorretly in Excel, or is it
something to do with linking the tables in Access?

Please help as I am very stuck here!!!

Thanks a lot any help will be appreciated!!!

Regards

Vivian

On May 28, 9:27*am, vivi wrote:
Hi all

I have created a master spreadsheet which I have written a code for in order
to:

1. Delete all contents except the first row, which I have written as :

Sheets("Data").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("a2").Select

2. It will look at the file path and file name (as the original files are
saved in the same foler on a monthly basis), and then select and copy all the
data within the specified file path and paste it onto my master spreadsheet,
which looks like this:

qpath = [D14].Value: qfile = [D15].Value

'Opening workbook specified within the worksheet and copy all data
Workbooks.Open(qpath & qfile).Activate
Sheets("Sheet1").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Corporate File Preparation").Activate
Sheets("Data").Select
Range("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Windows(qfile).Activate
Range("A2").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Corporate File Preparation").Activate
Sheets("Control").Select
Range("B2").Select

Eventually I have inserted a few columns at the end of the data for date
stamp and other forumlas as well.

However, my problem arises when I copy this module into other master
spreadsheets, the formats and data are all different between them so what I
did was to change the file paths and some of the formulas. Everything works
perfectly, except when I link these master spreadsheets to Access, which is 8
in total, 7 of them are incorrect, due to the original module I written for
consists of 27 columns (included 3 columns i have added in using vba) and 853
rows of data. The other spreadsheets have less columns and less rows of data,
excel looks fine, but in Access, many blank rows and columns are also
imported up to 27 columns and 853 rows. I am not sure what I need to do here
as I will have to append these sets of data to another table in Access, it
also append those are blank too.

I think it's because my vba are written incorretly in Excel, or is it
something to do with linking the tables in Access?

Please help as I am very stuck here!!!

Thanks a lot any help will be appreciated!!!

Regards

Vivian

Your VBA can be greatly simplified.

1:
Sheets("Data").Rows("2:65536").ClearContents

2: (could be simplified even further than this actually)
qpath = [D14].Value: qfile = [D15].Value
Workbooks.Open(qpath & qfile).Activate
Sheets("Sheet1").Rows("2:65536").Copy
Windows("Corporate File Preparation").Activate
Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
Windows(qfile).Close True
Windows("Corporate File Preparation").Activate
Sheets("Control").Range("B2").Select

Without seeing the rest of your code, it is hard to say whether the
VBA code is to blame for your access troubles. If your code is
inserting values (even blank spaces) into those unused rows/columns,
then that is probably what is causing your Access problems.

Thanks JW

These are my codes...

I have checked the files and use ctrl+end to look for the end of the data,
and it does indeed contains a whole of of blanks!! I am sure I havent done or
done something to the codes I've written ... (these are my first codes so I
don't really trust myself anyway)

Please help me to find a solution, I have been trying to do different things
since the morning :(

Thanks a lot

Vivian

codes:

Sub ImportData()

MsgBox ("Have you changed date to Today's Date yet")

Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Clearing out Previous Data

Sheets("Data").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("a2").Select

Sheets("Control").Select
qpath = [D14].Value: qfile = [D15].Value

'Opening workbook specified within the worksheet and copy all data
Workbooks.Open(qpath & qfile).Activate
Sheets("By Time Period").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Paste to Files Preparation Workbook
Windows("GSTT File Preparation").Activate
Sheets("Data").Select
Range("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Windows(qfile).Activate
Range("A2").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("GSTT File Preparation").Activate
Sheets("Control").Select
Range("B2").Select

'Inserting Dates and Converting Project IDs and Insert Function's Name
'Counting Number of records processes in Status bar

Sheets("Data").Select
qrow = 2

Do Until IsEmpty(Cells(qrow, 24))
If Not IsEmpty(Cells(qrow, 24)) And IsEmpty(Cells(qrow, 25)) Then
Cells(qrow, 25).Formula = "=Control!$D$7"
Cells(qrow, 26).Formula = "GSTT"
Cells(qrow, 27).FormulaR1C1 =
"=MID(RC[-22],FIND(""_"",RC[-22])+1,4)"
End If
qrow = qrow + 1
Loop
Range("A2").Select
Sheets("Control").Select
Range("B2").Select

MsgBox ("Preparation Complete!" & Chr(13) & Chr(13) & "Workbook will be
Saved automatically and Ready for Upload.")

ActiveWorkbook.Save

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub